MySQL 组合来自三个查询的数据

Question

有没有办法组合这些查询，以便我获得一个独特的名称，然后是今天、明天和后天的数量

select name, SUM(qty) as qty_today, 0 as qty_tomororow, 0 as qty_om  
from order_item
where delivery_date = '2011-11-22'
group by name
union all
select name, qty as qty_today, SUM(qty) as qty_tomororow, 0 as qty_om  
from order_item
where delivery_date = '2011-11-23'
group by name
union all
select name, 0 as qty_today, 0 as qty_tomororow, SUM(qty) as qty_om  
from order_item
where delivery_date = '2011-11-24'
group by name

更新
给定表格
名称、数量、交货日期
芝加哥, 1, 2011-11-22
纽约, 2, 2011-11-22
芝加哥, 3, 2011-11-23

我希望
芝加哥, 1, 3, 0
纽约, 1, 0, 0

Answer 1

如果您尝试将所有这些合并到一个数据集中，您可以尝试此操作，

SELECT name, 
       SUM(CASE WHEN delivery_date = '2011-11-22' THEN qty 
                ELSE 0 END) AS qty_today,
       SUM(CASE WHEN delivery_date = '2011-11-23' THEN qty 
                ELSE 0 END) AS qty_tomororow,
       SUM(CASE WHEN delivery_date = '2011-11-24' THEN qty 
                ELSE 0 END) AS qty_om
from order_item
where delivery_date between '2011-11-22' and '2011-11-24'
group by name

这适用于 TSQL (MS SQL)，希望它与 MySql 中的语法相同