others - SQL Server- 带有inner join的返回的结果比预期的要多

我有一个SQL查询:


SELECT 


 us.userid, 


 us.username, 


 CONVERT(VARCHAR(10), s.created, 101) AS LastLoginDate 


FROM


 users us 


INNER JOIN 


 session s ON us.userid = s.userid 


WHERE 


 supergroupid = 145577 


ORDER BY 


 us.userid DESC 



我如何只获得每个用户的会话表中最新的s.created条目,只有一个条目以及users表中的其他数据?

时间:

另外,你真的不应该在你的查询中格式化你的日期,这应该在表示层中完成。


select *


from


(


 SELECT us.userId, us.userName, CONVERT(VARCHAR(10),s.created, 101) as LastLoginDate


 , RowNum = ROW_NUMBER() over (partition by us.userId order by s.created desc)


 FROM users us


 inner join session s 


 on us.userId = s.userId


 where superGroupId = 145577


) x


where x.RowNum = 1


order by x.userid desc



我认为日期上带有Max()的GROUP BY可以:


SELECT us.userId, us.userName, MAX(CONVERT(VARCHAR(10),s.created, 101)) as LastLoginDate


 FROM users us


inner join session s 


 on us.userId = s.userId


 where superGroupId = 145577


 group by us.userId, us.userName


 order by us.userid desc



可以执行返回每个用户最近会话的子查询,并对它进行如下操作:


SELECT us.userId, us.userName, CONVERT(VARCHAR(10),s.created, 101) as LastLoginDate


FROM users us


INNER JOIN


(


 SELECT s.userId, s.created, ROW_NUMBER OVER(PARTITION BY s.userId ORDER BY s.created DESC) AS [RowNum]


 FROM session AS s



) s 


 on us.userId = s.userId


 where superGroupId = 145577 AND s.[RowNum] = 1


 order by us.userid desc



如果你想要最近会话的日期/时间,请使用max() ,但在转换为字符串之前!


select us.userId, us.userName, convert(varchar(10), max(s.created), 101) as LastLoginDate


from users us inner join


 session s 


 on us.userId = s.userId


where superGroupId = 145577


group by us.userid, us.userName;



使用这个查询:


SELECT userId, MAX(created) as LastLoginDate


FROM session


GROUP BY userId 



你将获得每个用户的最后一个登录日期,
然后将它连接到表users


SELECT us.userId, us.userName, CONVERT(VARCHAR(10), s.LastLoginDate, 101) as LastLoginDate


FROM users us INNER JOIN (


 SELECT userId, MAX(created) as LastLoginDate


 FROM session


 GROUP BY userId 


) s on s.userId = us.userId 


WHERE superGroupId = 145577


ORDER BY us.userid desc



你可以对每个会话进行rank,然后对最新的会话进行筛选。

比如。


SELECT * FROM (


SELECT DENSE_RANK() OVER (PARTITION BY US.USERID ORDER BY LASTLOGINDATE DESC) RNK


)


WHERE RNK = 1




;WITH ctelastlogin (userid, lastlogindate) 


 AS (SELECT userid, 


 Max(created) 


 FROM session 


 GROUP BY userid) 


SELECT us.userid, 


 us.username, 


 CONVERT(VARCHAR(10), cte.lastlogindate, 101) AS LastLoginDate 


FROM users us 


 INNER JOIN ctelastlogin cte 


 ON cte.userid = us.userid 


WHERE supergroupid = 145577 


ORDER BY us.userid DESC 



...