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) = 0
WHERE   type = 'P'
        AND number < 30;