需求说明:
我有一个客户表,还有一个客户的体验记录表,我需要汇总客户体验信息如下:
客户名称,本周体验次数,本月体验次数,本年度体验次数,累计体验次数,首次体验时间,最近体验时间 信息,如何用一个SQL语句搞定:
COUNT函数官方说明:指定 COUNT
返回唯一非 Null 值的数量。
所以变相实现如下:
SELECT me.CustomerCode, WeekExperienceCount = COUNT(CASE WHEN ExpDate>DATEADD(DAY,-7,GETDATE()) THEN 1 ELSE NULL END), MonthExperienceCount = COUNT(CASE WHEN ExpDate>DATEADD(MONTH,-1,GETDATE()) THEN 1 ELSE NULL END), YearExperienceCount = COUNT(CASE WHEN ExpDate>DATEADD(YEAR,-1,GETDATE()) THEN 1 ELSE NULL END), TotalExperienceCount = COUNT(me.RowUid), FirstExperienceDate = MIN(me.ExpDate), LastExperienceDate = MAX(me.ExpDate) FROM dbo.MD_Experience me GROUP BY me.CustomerCode
标签:COUNT,me,END,体验,ExpDate,Server,CountIF,SQL,NULL From: https://www.cnblogs.com/volts0302/p/18290021