mysql Group by X 并显示Total1(同表的Field = 0时)和Total2(Field = 1时)

Question

我有这个查询返回图像显示:

SELECT a.idarticle, c.stock, a.libelle as 'Libelle', sum(a.price * o.quantity) as `Total` from command c
inner join store s on c.idstore = s.idstore
inner join operation o on c.idcommand = o.idcommand
inner join article a on o.idarticle = a.idarticle
group by o.idarticle, c.stock

我想要:ID、Libelle、Total1(当 stock = 0 时)、Total2(当 stock = 1 时)

谢谢

Answer 1

我想这就是您要找的:

SELECT a.idarticle, a.libelle as 'Libelle', 
sum(case when stock = 0 then a.price * o.quantity) as `Total1` ,
sum(case when stock = 1 then a.price * o.quantity) as `Total2` 
from command c
inner join store s on c.idstore = s.idstore
inner join operation o on c.idcommand = o.idcommand
inner join article a on o.idarticle = a.idarticle
group by a.idarticle, a.libelle