我有一张桌子叫trx
trx_year trx_month Product number_of_trx
2018 4 A 100
2018 5 A 300
2018 3 A 500
2018 1 A 200
2018 2 A 150
2018 5 B 400
2018 2 B 200
2018 1 B 350
我想要结果：
每月订单号为trx的产品
我有这样的疑问：
select product,GROUP_CONCAT(number_of_trx order by trx_month)
from trx
where trx_year=2018
group by product
查询结果：
Product Data
A 200,150,500,100,300
B 350,200,400
但是，我想要这样的结果：（将月份的空值替换为0）
Product Data
A 200,150,500,100,300
B 350,200,0,0,400
我已经试过这样的方法了：（但结果和以前一样）
select product,GROUP_CONCAT(ifnull(number_of_trx,0) order by trx_month)
from trx
where trx_year=2018
group by product;
select product,GROUP_CONCAT(coalesce(number_of_trx,0) order by trx_month)
from trx
where trx_year=2018
group by product;
也许你能帮我，请检查http://sqlfiddle.com/#!9/f1ed4/3
最佳答案
这就是我想出来的。可能会更有效率，但你可以从中得到灵感。连接到产品表，而不是选择不同的产品。也扩大到包括超过5个月。
SELECT trx2.product, GROUP_CONCAT(trx2.total order by trx2.trx_month)
FROM
(SELECT temp2.product, temp2.trx_month, SUM(temp2.number_of_trx) AS total
FROM
(SELECT products.product, temp1.trx_month, temp1.number_of_trx
FROM (select 1 as trx_month, 0 as number_of_trx
UNION select 2, 0
UNION select 3, 0
UNION select 4, 0
UNION select 5, 0) as temp1,
(SELECT distinct product from trx) AS products
UNION ALL
SELECT trx.product, trx.trx_month, trx.number_of_trx
FROM trx) as temp2
GROUP BY temp2.product, temp2.trx_month) AS trx2
GROUP BY product