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

最好是这样显示

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

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

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

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

数据库是什么?看起来不是Oracle或者mysql

这个要用到行转列,你可以搜一下,不难的.

其中case time when '2019-04-17 11' then c else 0 end '2019-04-17 11',
case time when '2019-04-17 12' then c else 0 end '2019-04-17 12',
case time when '2019-04-17 13' then c else 0 end '2019-04-17 13',
case time when '2019-04-17 14' then c else 0 end '2019-04-17 14',
case time when '2019-04-17 15' then c else 0 end '2019-04-17 15',
case time when '2019-04-17 16' then c else 0 end '2019-04-17 16',
case time when '2019-04-17 17' then c else 0 end '2019-04-17 17',
case time when '2019-04-17 18' then c else 0 end '2019-04-17 18'
这个里面的值在你的编程语言中得出然后拼接sql就可以了,
这个是统计每个名称在每个小时创建的次数,
i表就是你现在查出的表(你的sql去掉productId='xxxx')

以前也遇到过
最后是写一个定时任务，每天跑一次，存到新建的表中，然后就直接查那张表就有你想要的效果了

按时间和产品分组统计数量，肯定可以统计出来