gpt4 book ai didi

SQL 按选择分组

转载 作者:行者123 更新时间:2023-12-02 10:19:52 25 4
gpt4 key购买 nike

我正在使用 SQL Server,并且我有一个包含以下列的表:

SessionId | Date | first name | last name 

我想要按 sessionId 进行分组,然后获取具有最大日期的行。

例如:

xxx | 21/12/2012 | f1 | l1
xxx | 20/12/2012 | f2 | l2
yyy | 21/12/2012 | f3 | l3
yyy | 20/12/2012 | f4 | l4

我想获得以下行:

xxx | 21/12/2012 | f1 | l1
yyy | 21/12/2012 | f3 | l3

谢谢

最佳答案

试试这个:

WITH MAXSessions
AS
(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY SessionID ORDER BY Date DESC) rownum
FROM Sessions
)
SELECT
SessionId,
Date,
firstname,
lastname
FROM MAXSessions
WHERE rownum = 1;

或者:

SELECT 
s.SessionId,
s.Date,
s.firstname,
s.lastname
FROM Sessions s
INNER JOIN
(
SELECT SessionID, MAX(Date) LatestDate
FROM sessions
GROUP BY SessionID
) MAxs ON maxs.SessionID = s.SessionID
AND maxs.LatestDate = s.Date;
<小时/>

更新:要获取 session 计数,您可以执行以下操作:

SELECT 
s.SessionId,
s.Date,
s.firstname,
s.lastname,
maxs.SessionsCount
FROM Sessions s
INNER JOIN
(
SELECT SessionID, COUNT(SessionID), SessionsCount, MAX(Date) LatestDate
FROM sessions
GROUP BY SessionID
) MAxs ON maxs.SessionID = s.SessionID
AND maxs.LatestDate = s.Date;

关于SQL 按选择分组,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14111189/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com