如何选择，使用SQL

问题描述：

一列中的最大值的所有行我有一个这样的表：如何选择，使用SQL

+----+---------+---------------------+ 
| id | user_id |  start_date  | 
+----+---------+---------------------+ 
| 1 |  1 | 2014-02-01 00:00:00 | 
| 2 |  1 | 2014-01-01 00:00:00 | 
| 3 |  2 | 2014-01-01 00:00:00 | 
| 4 |  2 | 2014-01-01 00:00:00 | 
| 5 |  3 | 2015-01-01 00:00:00 | 
+----+---------+---------------------+

我该怎么选择，为每个用户，都行：

开始日期NOW（）和
最大起始日期

所以对于示例行，输出应该是：

+----+---------+---------------------+ 
| id | user_id |  start_date  | 
+----+---------+---------------------+ 
| 1 |  1 | 2014-02-01 00:00:00 | // this is a single maximum date within that user 
| 3 |  2 | 2014-01-01 00:00:00 | // these two share maximum start date 
| 4 |  2 | 2014-01-01 00:00:00 | 
+----+---------+---------------------+

什么我到目前为止是这样的：

SELECT t.* FROM ticket t 
    JOIN (
     SELECT start_date, MAX(start_date) FROM ticket /* GROUP BY user_id */ 
    ) highest 
    ON t.start_date = highest.start_date 
    WHERE t.start_date <= NOW();

但可根据需要，这并不工作。我在好路上吗？

答

你在正确的轨道上，有点。在你的派生表，你需要得到每个用户ID的最大日期，所以：

SELECT user_id, 
     MAX(start_date) as MaxDate 
     FROM ticket 
     GROUP BY user_id

然后你就可以加入到对开始日期和用户ID：

SELECT t.* FROM ticket t 
    JOIN (
     SELECT user_id, 
     MAX(start_date) as MaxDate 
     FROM ticket 
     GROUP BY user_id 
    ) highest 
    ON t.start_date = highest.maxdate 
    and t.user_id = highest.user_id 
    WHERE t.start_date <= NOW();

SQL Fiddle

+1以几秒钟击败我，得到同样的答案:) – 2014-10-10 16:11:33

答

_try：

SELECT T.* FROM ticket AS T 
JOIN (SELECT 
    [User_Id] 
    ,MAX([Start_Date]) AS Start_Date 
FROM ticket 
WHERE Start_Date <= GETDATE() 
GROUP BY User_Id) AS Grouped ON T.User_Id = Grouped.User_Id AND T.Start_Date = Grouped.Start_Date 
ORDER BY Id 
DROP TABLE #This

如何选择，使用SQL

相关推荐