代码之家 › 专栏 › 技术社区 › Brad

获取不同记录的总和并比较两个日期范围

compare sql mysql

0

Brad · 技术社区 · 4 年前

我有一个名为purchases的表,其中有列:name、amount、city、date。

我想返回金额总和,并按金额最多的城市订购,并比较两个日期范围。

这是一把小提琴: http://sqlfiddle.com/#!9/6b2017/6

我可以按照sum(amount)desc(时段1)的顺序获得第一组城市的sum(amount):

select distinct city, sum(amount) as total
from purchases
where date between '2020-07-01' and '2020-10-31'
group by city
order by sum(amount) desc
limit 3

以上查询返回城市:塔尔萨、纽约、芝加哥

但我也希望在前3个月的同一个查询中获得,但在前一个查询中返回的城市相同,类似于此,但正如我提到的,我希望在一个查询中返回它(第2阶段)

select city, sum(amount) as total
from purchases
where city in ('Tulsa', 'New York', 'Chicago')
  and date between '2020-03-01' and '2020-06-31'
group by city

希望返回城市(按照它们在第一次查询中的顺序),每个日期范围的总和(金额),因此理论上:

塔尔萨第1期:总额(金额) 塔尔萨第2期:总额(金额) 纽约时段1:总额(金额) 纽约第2期:总额(金额) 芝加哥时段1:总和(金额) 芝加哥时段2:总和(金额)

非常感谢您的帮助

1 回复 | 直到 4 年前

1

2

forpas 4 年前

使用 LEFT 将查询联接到表并再次聚合:

SELECT t.city, t.total period1, COALESCE(SUM(p.amount), 0) period2
FROM (
  SELECT city, SUM(amount) AS total 
  FROM purchases 
  WHERE date BETWEEN '2020-07-01' AND '2020-10-31' 
  GROUP BY city 
  ORDER BY total DESC LIMIT 3
) t LEFT JOIN (
  SELECT city, amount
  FROM purchases
  WHERE date BETWEEN '2020-03-01' AND '2020-06-30'
) p ON p.city = t.city
GROUP BY t.city, t.total
ORDER BY t.total DESC;

或者,使用条件聚合:

SELECT city,
       SUM(CASE WHEN date BETWEEN '2020-07-01' AND '2020-10-31' THEN amount ELSE 0 END) period1,
       SUM(CASE WHEN date BETWEEN '2020-03-01' AND '2020-06-30' THEN amount ELSE 0 END) period2
FROM purchases
GROUP BY city
ORDER BY period1 DESC LIMIT 3;

或:

SELECT city,
       SUM(CASE WHEN date >= '2020-07-01' THEN amount ELSE 0 END) period1,
       SUM(CASE WHEN date < '2020-07-01' THEN amount ELSE 0 END) period2
FROM purchases
WHERE date BETWEEN '2020-03-01' AND '2020-10-31'
GROUP BY city
ORDER BY period1 DESC LIMIT 3;

请参见 demo .