SQL 某月每天的数据 或者某天每小时的数据 或者 某段时间每个月月数据，和该月每天的数据

---1.当月每一天的汇总

SELECT   SUM(Qty)Qty ,  DATEPART(DAY,pickDate) Pickday FROM dbo.Delivery 

WHERE  DATEDIFF(MONTH, pickDate, GETDATE()) < 1

GROUP BY  DATEPART(DAY, pickDate)

ORDER BY pickday  



----2.当天每一个小时的汇总
--通过系统函数获取24小时内的数据 
select number INTO #NumInfo from master..spt_values where type='P' and number<24

SELECT b.number,  ISNULL(temp.Qty,0)Qty FROM (
SELECT   SUM(Qty)Qty ,  DATEPART(hour,pickDate) Pickday FROM dbo.Delivery  
 WHERE  DATEDIFF(DAY,pickDate,GETDATE())=0
GROUP BY  DATEPART(hour,pickDate)
  ) temp  RIGHT JOIN #NumInfo b ON temp.Pickday=b.number

  DROP TABLE  #NumInfo

----3.某段时间每个月月数据，和该月每天的数据

SELECT T.Data '月份',T.TotalQty '月总出库数' , (T.TotalQty / T.Days) AS '每日平均出库数' FROM (
SELECT SUM(A.QTY)TotalQty,a.Data,a.Days FROM (
SELECT b.QTY , CONVERT(varchar(7), a.StartTime,23) 'Data'
--根据日期获取每个月一共多少天
, DatePart(day,DateAdd(day,-1,CONVERT(varchar(7), a.StartTime,23)+'-01')) 'Days'
FROM OutStockCmd a INNER JOIN GoodsTag b ON a.GoodsTagID= b.ID
)A GROUP BY Data,a.Days
) T ORDER BY Data

月份          月总出库数    每日平均出库数
2017-09     856720       27636
2017-10     2018135      67271
2017-11    3027212      97652
2017-12    6602995     220099
2018-01   15909367    513205