sqlserver 的查询最近7天的数据 group by 与with的结合 以及系统表的妙用

--测试数据

;WITH tab(Date,zt)AS(select  CheckInDate as name,count(*) as value  from Base_Households group by CheckInDate)SELECT  '第'+ltrim(Str(DAY(DATEDIFF(DAY,DATEADD(DAY, -number, GETDATE()), GETDATE()))))+'天' AS name, ISNULL(zt, 0) AS value FROM    master..spt_values        LEFT JOIN tab ON DATEDIFF(DAY, DATEADD(DAY, -number, GETDATE()),                         tab.Date) = 0WHERE   type = 'P'        AND number < 30;