SQL语句统计每天的数据

按用户注册时间统计每天注册的不同来源、不同状态的用户数量：

SELECT  CONVERT(VARCHAR(100), RegisterTime, 23) RDate ,--DATEPART(YEAR, RegisterTime) 年
        CASE WHEN CustomerTypeSysNo = 1
                  AND Status = 0 THEN ISNULL(COUNT(SysNo), 0)
        END 'AWaitForAudit' ,
        CASE WHEN CustomerTypeSysNo = 1
                  AND Status = 1 THEN ISNULL(COUNT(SysNo), 0)
        END 'AAudited' ,
        CASE WHEN CustomerTypeSysNo = 2
                  AND Status = 1 THEN ISNULL(COUNT(SysNo), 0)
        END 'BAudited'
INTO    #Temp
FROM    dbo.Customer
WHERE   RegisterTime BETWEEN '2015-11-23 00:00:00'
                     AND     '2015-11-25 23:59:59'
GROUP BY CustomerTypeSysNo ,
        RegisterTime ,
        Status;

SELECT  RDate ,
        ISNULL(SUM(AWaitForAudit), 0) 'AWaitForAudit' ,
        ISNULL(SUM(AAudited), 0) 'AAudited' ,
        ISNULL(SUM(BAudited), 0) 'BAudited'
FROM    #Temp
GROUP BY RDate;

DROP TABLE #Temp;

 

转载于:https://www.cnblogs.com/LiuLiangXuan/p/5065975.html