declare @sDate VARCHAR(10) DECLARE @eDate VARCHAR(10) SET @sdate = ‘2014-06-25‘ SET @edate = ‘2014-06-26‘ --生成日期表CREATE TABLE #DateTable([date] VARCHAR(10)) WHILE (@sdate <=@edate) BEGIN     INSERT  #DateTable([date]) VALUES(@sdate)     --select CONVERT(VARCHAR(10), DATEADD(day, 1, @sdate), 120)    SET @sdate =CONVERT(VARCHAR(10), DATEADD(day, 1, @sdate), 120)  END --SELECT * FROM  #DateTable --查出所有的聊天实例--SELECT * INOT #ChatOnline FROM ChatOnline WHERE  --查出所有的聊天记录SELECT b.ID AS LogID, a.ID AS ChatOnlineID, a.cvMainID, a.CaMainID, a.Initiative, a.StartDate, a.EndDate, CONVERT(VARCHAR(10), b.AddDate, 120) AS AddDate, b.SenderType, b.IsViewedINTO #LogTableFROM ChatOnline a, ChatOnlineLog b WHERE a.ID=b.ChatOnlineID AND ManagerUserID=0 --AND b.AddDate BETWEEN @sdate AND @edate--SELECT * FROM #LogTable--生成发起聊天数量表SELECT DISTINCT ChatOnlineID, cvMainID, caMainID, Initiative, AddDate INTO #ChatOnline FROM #LogTable--SELECT * FROM #ChatOnline--查询结果，赋值为0的，取出后在C#内计算SELECT     b.[date],--日期    InitiativeCount =(SELECT COUNT(ChatOnlineID) FROM #ChatOnline WHERE AddDate=b.[date]), --会话次数    SendCount = (SELECT COUNT(LogID) FROM #LogTable WHERE Initiative=SenderType AND AddDate=b.[date]), --发送条数    ReceiveCount = (SELECT COUNT(LogID) FROM #LogTable WHERE Initiative!=SenderType AND AddDate=b.[date]), --发送条数    PaUsageCount =(SELECT COUNT(ID) FROM ChatOnline WHERE  CvMainID!=0 AND AddDate=b.[date]),--求职者主动使用次数    PaLogInCount = (SELECT COUNT(ID) FROM PaLoginLog WHERE AddDate=b.[date]),--求职者总登录次数    PaUsagePercent=0,--求职者主动使用比例    CaUsageCount = (SELECT COUNT(ID) FROM ChatOnline WHERE caMainID!=0 AND AddDate=b.[date]),--企业主动使用次数    CaLogInCount=(SELECT COUNT(ID) FROM CaLoginLog WHERE AddDate=b.[date]),--企业总登录次数    CaUsagePercent=0,--企业主动使用比例    AvgUsageCountWhenLog=0,--使用次数除以登录次数    AvgSendCountPerChat = 0,--对话平均发送条数    UnReadChatCount,--留言次数（发送，对方没回复）    UnReadCount = (SELECT COUNT(LogID) FROM #LogTable WHERE IsViewed=0 AND AddDate=b.[date]) --留言条数FROM #LogTable a RIGHT JOIN #DateTable b ON a.AddDate=b.[date]WHERE 1=1GROUP BY b.[date]DROP TABLE  #DateTableDROP TABLE #LogTableDROP TABLE #ChatOnline