TSQL选择最大 [英] TSQL Select Max

问题描述

Userid   FirstName   LastName        UserUpdate 
1        Dan         Kramer          1/1/2005  
1        Dan         Kramer          1/1/2007  
1        Dan         Kramer          1/1/2009  
2        Pamella     Slattery        1/1/2005  
2        Pam         Slattery        1/1/2006  
2        Pam         Slattery        1/1/2008  
3        Samamantha  Cohen           1/1/2008  
3        Sam         Cohen           1/1/2009  

我需要为所有这些用户提取最新的更新，基本上这就是我要寻找的内容:

I need to extract the latest updated for all these users, basically here's what I'm looking for:

Userid   FirstName   LastName        UserUpdate  
1        Dan         Kramer          1/1/2009     
2        Pam         Slattery        1/1/2008   
3        Sam         Cohen           1/1/2009  

现在，当我运行以下命令时:

Now when I run the following:

SELECT Userid，FirstName，LastName，Max(UserUpdate)AS MaxDate 从表 GROUP BY用户名，名字，姓氏

SELECT Userid, FirstName, LastName, Max(UserUpdate) AS MaxDate FROM Table GROUP BY Userid, FirstName, LastName

我仍然得到重复的东西，像这样:

I still get duplicates, something like this:

Userid   FirstName   LastName        UserUpdate 
1        Dan         Kramer          1/1/2009  
2        Pamella     Slattery        1/1/2005  
2        Pam         Slattery        1/1/2008  
3        Samamantha  Cohen           1/1/2008  
3        Sam         Cohen           1/1/2009 

推荐答案

尝试:

declare @Table table (userid int,firstname varchar(10),lastname varchar(20), userupdate datetime)
INSERT @Table VALUES (1, 'Dan'         ,'Kramer'          ,'1/1/2005')  
INSERT @Table VALUES (1, 'Dan'         ,'Kramer'          ,'1/1/2007')  
INSERT @Table VALUES (1, 'Dan'         ,'Kramer'          ,'1/1/2009')  
INSERT @Table VALUES (2, 'Pamella'     ,'Slattery'        ,'1/1/2005')  
INSERT @Table VALUES (2, 'Pam'         ,'Slattery'        ,'1/1/2006')  
INSERT @Table VALUES (2, 'Pam'         ,'Slattery'        ,'1/1/2008')  
INSERT @Table VALUES (3, 'Samamantha'  ,'Cohen'           ,'1/1/2008')
INSERT @Table VALUES (3, 'Sam'         ,'Cohen'           ,'1/1/2009') 

SELECT
    dt.Userid,dt.MaxDate
        ,MIN(a.FirstName) AS FirstName, MIN(a.LastName) AS LastName
    FROM (SELECT 
              Userid, Max(UserUpdate) AS MaxDate 
              FROM @Table GROUP BY Userid
         ) dt
        INNER JOIN @Table a ON dt.Userid=a.Userid and dt.MaxDate =a.UserUpdate
    GROUP BY dt.Userid,dt.MaxDate

输出:

Userid      MaxDate                 FirstName  LastName
----------- ----------------------- ---------- --------------------
1           2009-01-01 00:00:00.000 Dan        Kramer
2           2008-01-01 00:00:00.000 Pam        Slattery
3           2009-01-01 00:00:00.000 Sam        Cohen

这篇关于TSQL选择最大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！