mysql - 重新排列每个月的 MySQL 表

Question

这是我的目标表

retail_id   month   tgt_volume   product_type1         2013-11-01    2        Bar2         2013-10-01    1        Touch3         2013-09-01    1        Bar4         2013-10-01    5        Smart5         2013-10-01    8        Bar3         2013-08-01    2        Smart2         2013-08-01    5        Bar3         2013-07-01    7        Bar3         2013-07-01    2        Smart

I need this format

retail_id  bar  smart   touch   total   month1           2    0       0       2       2013-11-012           0    0       1       1       2013-10-01     2           5    0       0       5       2013-08-013           1    0       0       1       2013-09-01 3           0    2       0       2       2013-08-01 3           7    2       0       9       2013-07-014           0    5       0       5       2013-08-015           8    0       0       8       2013-10-01  

So I want to retrieve every months total target and each product type total for each retail_id. I was trying a query which count every product_type but not count each month. How can I do this to make above format.. my test query is(without month count)

SELECT   DISTINCT t.retail_id,bar_vol.bar,smart_vol.smart,touch_vol.touch,total.total,t.month FROM targets t
LEFT JOIN
(SELECT SUM(tgt_volume) AS bar,retail_id FROM targets t1 WHERE t1.product_type='Bar' GROUP BY retail_id) AS bar_vol  
ON  t.retail_id=bar_vol.retail_id
LEFT JOIN
(SELECT SUM(tgt_volume) AS smart,retail_id FROM targets t2 WHERE t2.product_type='Smart' GROUP BY retail_id) AS smart_vol  
ON  t.retail_id=smart_vol.retail_id
LEFT JOIN
(SELECT SUM(tgt_volume) AS touch,retail_id FROM targets t3 WHERE t3.product_type='Touch' GROUP BY retail_id) AS touch_vol  
ON  t.retail_id=touch_vol.retail_id
LEFT JOIN
(SELECT SUM(tgt_volume) AS total,retail_id FROM targets t4  GROUP BY retail_id) AS total  
ON  t.retail_id=total.retail_id

Answer 1

您可以在聚合函数内使用 CASE 语句来实现此目的:

SELECT  Retail_ID,
        SUM(CASE WHEN product_Type = 'Bar' THEN tgt_Volume ELSE 0 END) AS Bar,
        SUM(CASE WHEN product_Type = 'smart' THEN tgt_Volume ELSE 0 END) AS Smart,
        SUM(CASE WHEN product_Type = 'Touch' THEN tgt_Volume ELSE 0 END) AS Touch,
        SUM(tgt_Volume) AS Total,
        Month
FROM    targets 
GROUP BY RetailId, Month;

<强> Example on SQL Fiddle