我的精妙改版

旧语句，一个是业务不对，一个是性能不佳

SELECT   ResID,ResType,UserID,Account,NickName,ResName,ResIntroduce,sum(TicketCount) as SumTicketCount 
FROM PandaMonthTicketComment (NOLOCK) 
where  ResID<>0 and Right(ResID,2)<>'11' and restype<10 and AddTime BETWEEN  DATEADD(DAY,0,CONVERT(VARCHAR(8),GETDATE(),112)) AND GETDATE() 
GROUP BY  ResID,ResType,UserID,Account,NickName,ResName,ResIntroduce order by SumTicketCount desc

 

新语句：

 

SELECT A.ResID,A.ResType,A.UserID,A.Account,A.NickName,A.ResName,A.ResIntroduce,B.SumTicketCount FROM PandaMonthTicketComment AS A 
RIGHT JOIN
(
SELECT ResID,sum(TicketCount) as SumTicketCount,MAX(ADDTime) AS TicketAddtime
FROM PandaMonthTicketComment (NOLOCK) 
where  ResID<>0 and AddTime BETWEEN  DATEADD(DAY,0,CONVERT(VARCHAR(8),GETDATE(),112)) AND GETDATE() 
GROUP BY ResID,ResType
) AS B
ON A.RESID=B.ResID AND A.AddTime=B.TicketAddtime
ORDER BY B.SumTicketCount DESC

 

 

解决下面这些问题：

下面是我改造后的语句
1： 解决了多记录重复问题
2： 解决原来你的那种写法会导致不能sum汇总，就是 旧的语句《捡个仙女做老婆》 只有一个月票， 而新的语句  可以统计出  2 个月票


改语句重点在于引入：  MAX(ADDTime) AS TicketAddtime 然后使用 RIGHT JOIN  ， 通过变相的方式完成业务 。