sql统计的问题

悬赏园豆：10 [已解决问题] 解决于 2015-06-16 22:47

代码：

if Object_id('Tempdb..#tmp') IS NOT NULL    DROP TABLE #tmp;
 declare @pageCount int,@pageIndex int select @pageCount = 20,@pageIndex = 1; 
                                             with cte as( select * from (
                                                            select ROW_NUMBER() OVER(Order by a.UserID ) as row--分页 
                                                                  ,count(b.UserID) over(PARTITION BY b.UserID) as xsCarCount--车辆数量
                                                                  ,count(b.UserID) over(PARTITION BY b.UserID ) as yqUserCount--客户数量
                                                                  ,ROW_NUMBER() OVER(PARTITION BY a.UserID ORDER BY a.UserID) as rownum--过滤重复
                                                                  ,a.[UserID]
                                                                  ,a.[UserName]
                                                                  ,a.[UserType]
                                                                  ,a.[HoldID]
                                                                  ,a.[StartTime]
                                                                  ,a.[EndTime]
                                                                  ,a.[Email]
                                                                  ,a.[MobilePhone] 
                                                               from std_UserInfo a  with (nolock) 
                                                                    left join std_UserInfo c on a.MobilePhone=c.InviteCode
                                                                    left hash join std_UserObj  b on c.UserID=b.UserID 
                                                               where a.U_UserType=1  and a.holdID=10 and a.isDeleted=0 and a.StartTime BETWEEN '2015-06-11 00:00:00' and '2015-06-11 15:01:32'   
                                                             )temp where temp.rownum=1
                                           )
                                select * into #tmp from cte;
                                select * from #tmp  where  row  between (@pageIndex-1)*@pageCount+1  and  @pageCount*@pageIndex ;
                                select count(1) as countNumber  from #tmp

博客园代码格式问题导致不好看星哥你提出到sql里面看吧

结果

yq的userCount有问题

原因

SELECT[UserID], [UserName],[MobilePhone],InviteCode FROM std_UserInfo WHERE USERNAME ='11112222'
SELECT  [UserID],[UserName],[MobilePhone],InviteCode FROM std_UserInfo WHERE USERNAME ='22223333'

SELECT * FROM STD_USEROBJ WHERE USERID=845

--用户为11112222的 他邀请的用户为22223333 (用户11112222的MobilePhone等于用户为 22223333的InviteCode) 
--总结 11112222下面有1个用户 同理 要是其他的用户的InviteCode也等于11112222 MobilePhone 则有2 个

--STD_USEROBJ 下面的2223333的userid 为 845说明有3个车 这个统计没有问题