这样SQL怎么写？

Question

SELECT i.item_id, sum(i.number) AS number FROM item AS i, enter AS e WHERE e.id = i.other_id AND i.type = 0 AND e.created_at <= 10000 AND e.created_at >= 0 GROUP BY i.item_id

SELECT i.item_id, sum(i.number) AS number FROM item AS i, enter AS e WHERE e.id = i.other_id AND i.type = 1 AND e.created_at <= 10000 AND e.created_at >= 0 GROUP BY i.item_id

这样的两条SQL可以合并吗？现在单条显示的是：

想合并显示为：

Answer 1

有一种是 join 方案，将两个查询语句看成是两个表，代码如下

select t1.item_id, t1.number ,t2.number from 
(
SELECT i.item_id, sum(i.number) AS number FROM item AS i, enter AS e WHERE e.id = i.other_id AND i.type = 0 AND e.created_at <= 10000 AND e.created_at >= 0 GROUP BY i.item_id ) t1 left join 
(
SELECT i.item_id, sum(i.number) AS number FROM item AS i, enter AS e WHERE e.id = i.other_id AND i.type = 1 AND e.created_at <= 10000 AND e.created_at >= 0 GROUP BY i.item_id ) t2 on t1.item_id = t2.item_id

另一种是用两个 sum ，里面用 case when 语句 ，代码如下

SELECT i.item_id, sum(case i.type when 0 then i.number else 0 end ),sum(case i.type when 1 then i.number else 0 end ) AS number FROM item AS i, enter AS e WHERE e.id = i.other_id  AND e.created_at <= 10000 AND e.created_at >= 0 GROUP BY i.item_id