我想请教下产品每天的数量总和，但时间不确定

Question

最好是这样显示

产品表Base_Product，产品详情表Client_Order，关联ProductID

根据Client_Order中的createDate每天分组显示，

我现在是只能一个产品按时间分组显示出来

Answer 1

SELECT 

     ProductName,
     SUM(CASE WHEN DAY(CreateDate)=1 THEN num ELSE 0 END) 一号,
     SUM(CASE WHEN DAY(CreateDate)=2 THEN num ELSE 0 END) 二号,
     SUM(CASE WHEN DAY(CreateDate)=3 THEN num ELSE 0 END) 三号,
     SUM(CASE WHEN DAY(CreateDate)=4 THEN num ELSE 0 END) 四号,
     SUM(CASE WHEN DAY(CreateDate)=5 THEN num ELSE 0 END) 五号,
     SUM(CASE WHEN DAY(CreateDate)=6 THEN num ELSE 0 END) 六号,
     SUM(CASE WHEN DAY(CreateDate)=7 THEN num ELSE 0 END) 七号,
     SUM(CASE WHEN DAY(CreateDate)=8 THEN num ELSE 0 END) 八号,
     SUM(CASE WHEN DAY(CreateDate)=9 THEN num ELSE 0 END) 九号,
     SUM(CASE WHEN DAY(CreateDate)=10 THEN num ELSE 0 END) 十号,

     SUM(CASE WHEN DAY(CreateDate)=11 THEN num ELSE 0 END) 十一号,
     SUM(CASE WHEN DAY(CreateDate)=12 THEN num ELSE 0 END) 十二号,
     SUM(CASE WHEN DAY(CreateDate)=13 THEN num ELSE 0 END) 十三号,
     SUM(CASE WHEN DAY(CreateDate)=14 THEN num ELSE 0 END) 十四号,
     SUM(CASE WHEN DAY(CreateDate)=15 THEN num ELSE 0 END) 十五号,
     SUM(CASE WHEN DAY(CreateDate)=16 THEN num ELSE 0 END) 十六号,
     SUM(CASE WHEN DAY(CreateDate)=17 THEN num ELSE 0 END) 十七号,
     SUM(CASE WHEN DAY(CreateDate)=18 THEN num ELSE 0 END) 十八号,
     SUM(CASE WHEN DAY(CreateDate)=19 THEN num ELSE 0 END) 十九号,
     SUM(CASE WHEN DAY(CreateDate)=20 THEN num ELSE 0 END) 二十号,

     SUM(CASE WHEN DAY(CreateDate)=21 THEN num ELSE 0 END) 二一号,
     SUM(CASE WHEN DAY(CreateDate)=22 THEN num ELSE 0 END) 二二号,
     SUM(CASE WHEN DAY(CreateDate)=23 THEN num ELSE 0 END) 二三号,
     SUM(CASE WHEN DAY(CreateDate)=24 THEN num ELSE 0 END) 二四号,
     SUM(CASE WHEN DAY(CreateDate)=25 THEN num ELSE 0 END) 二五号,
     SUM(CASE WHEN DAY(CreateDate)=26 THEN num ELSE 0 END) 二六号,
     SUM(CASE WHEN DAY(CreateDate)=27 THEN num ELSE 0 END) 二七号,
     SUM(CASE WHEN DAY(CreateDate)=28 THEN num ELSE 0 END) 二八号,
     SUM(CASE WHEN DAY(CreateDate)=29 THEN num ELSE 0 END) 二九号,
     SUM(CASE WHEN DAY(CreateDate)=30 THEN num ELSE 0 END) 三十号,
     SUM(CASE WHEN DAY(CreateDate)=31 THEN num ELSE 0 END) 三十一号
FROM Base_Product a
JOIN Client_Order b ON a.ProductID=b.ProductID
WHERE CreateDate BETWEEN '2019-04-01' AND '2019-04-30'
GROUP BY ProductName