获取不同记录的总和，并在两个日期范围之间进行比较

Question

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

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

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

我可以按照sum(amount(desc(period 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：总(金额(

感谢提供的任何帮助

Answer 1

使用查询到表的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;

请参阅演示