Mysql 仅从最新日期中选择不同的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16979136/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql Select distinct records from latest dates only
提问by Samik Sengupta
This is my table structure -
这是我的表结构-
TABLE : COURSE_LOG
-----------------------------------------------------
| ID | USERNAME | COURSE_ID | LDATE |
-----------------------------------------------------
| 1 | user1 | 22 | 2013-06-01 |
-----------------------------------------------------
| 2 | user1 | 54 | 2013-06-03 |
-----------------------------------------------------
| 3 | user1 | 22 | 2013-06-03 |
-----------------------------------------------------
| 4 | user2 | 71 | 2013-06-04 |
-----------------------------------------------------
I want to pick all of user1's COURSE_IDdata distinctly (along with it's date). Since date will vary between two same COURSE_IDentries, I want to pick the row with the more recent date. I am hoping to get a result like this -
我想清楚地选择所有user1的COURSE_ID数据（连同它的日期）。由于日期在两个相同的COURSE_ID条目之间会有所不同，我想选择日期较近的行。我希望得到这样的结果——
-----------------------------
| COURSE_ID | LDATE |
-----------------------------
| 54 | 2013-06-03 |
-----------------------------
| 22 | 2013-06-03 |
-----------------------------
| 71 | 2013-06-04 |
-----------------------------
And I would not want this -
我不想要这个 -
-----------------------------
| 22 | 2013-06-01 | // THIS SHOULD BE OMITTED FROM RESULT BECAUSE THERE
----------------------------- // IS ANOTHER RECENT ENTRY WITH THE SAME COURSE_ID
I'm using this query -
我正在使用这个查询 -
SELECT DISTINCT(COURSE_ID), LDATE FROM COURSE_LOG
WHERE USERNAME = 'user1'
AND LDATE = (
SELECT LDATE
FROM COURSE_LOG
WHERE USERNAME = 'user1'
ORDER BY LDATE DESC
LIMIT 1
)
But it only picks one row. How do I correct this?
但它只选择一行。我该如何纠正？
回答by Meherzad
Try this query
试试这个查询
If you want only for user1then use this query:
如果您只想user1使用此查询：
select username, course_id, max(ldate) as date
from tbl
where username='user1'
group by course_id
SQL FIDDLE
SQL 小提琴
| USERNAME | COURSE_ID | DATE |
-------------------------------------
| user1 | 22 | 2013-06-03 |
| user1 | 54 | 2013-06-03 |
If you want to find latest date for all users then use this query
如果要查找所有用户的最新日期，请使用此查询
select username, course_id, max(ldate) as date
from tbl
group by username, course_id
In this query data of user2will also be included
在此查询数据中user2也将包括
| USERNAME | COURSE_ID | DATE |
-------------------------------------
| user1 | 22 | 2013-06-03 |
| user1 | 54 | 2013-06-03 |
| user2 | 71 | 2013-06-04 |
回答by KaeL
You can use MAX:
您可以使用MAX：
SELECT COURSE_ID
, MAX(LDATE) AS LDATE
FROM COURSE_LOG
WHERE USERNAME = 'user1'
GROUP BY COURSE_ID;
