有如下一个表S,问题如下:根据Item分类,输出Item和 当任一个Item的red总数大于blue总数时,red减去blue的差额,请问如何写查询语句?Item Color QuantityTable Red 100
Table Blue 50
Book Red 200
Book Red 100
Book Blue 400
Chair Red 300
Chair Blue 150
Chair Blue 50
Table Blue 50
Book Red 200
Book Red 100
Book Blue 400
Chair Red 300
Chair Blue 150
Chair Blue 50
sum(case when Color='Red' then Quantity else 0 end)-
sum(case when Color='Blue' then Quantity else 0 end) as 差额
from s
group by Item
having sum(case when Color='Red' then Quantity else 0 end)>
sum(case when Color='Blue' then Quantity else 0 end)
可以
select t1.item,t1.Quantity-t2.Quantity as 差额
from (
select Item,sum(Quantity) as Quantity
from s
where Color='Red'
group by Item
) as t1
inner join (
select Item,sum(Quantity) as Quantity
from s
where Color='Blue'
group by Item
) as t2
on t1.item=t2.item
where t1.Quantity>t2.Quantity