Mysql仅从最新日期中选择不同的记录 [英] Mysql Select distinct records from latest dates only

问题描述

这是我的表结构-

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 |
-----------------------------------------------------

我想清楚地选择所有user1的COURSE_ID数据(以及日期).由于日期在两个相同的COURSE_ID条目之间会有所不同，因此我想选择具有较新日期的行.我希望得到这样的结果-

I want to pick all of user1's COURSE_ID data distinctly (along with it's date). Since date will vary between two same COURSE_ID entries, I want to pick the row with the more recent date. I am hoping to get a result like this -

-----------------------------
|  COURSE_ID   |    LDATE   |
-----------------------------
|      54      | 2013-06-03 |
-----------------------------
|      22      | 2013-06-03 |
-----------------------------
|      71      | 2013-06-04 |
-----------------------------

我不想要这个-

-----------------------------    
|      22      | 2013-06-01 |    // THIS SHOULD BE OMITTED FROM RESULT BECAUSE THERE
-----------------------------    // IS ANOTHER RECENT ENTRY WITH THE SAME COURSE_ID

我正在使用此查询-

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?

推荐答案

尝试此查询

如果仅需要user1，请使用以下查询:

If you want only for user1 then use this query:

select username, course_id, max(ldate) as date
from tbl 
where username='user1'
group by course_id

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

此查询中还将包含user2的数据

In this query data of user2 will also be included

| USERNAME | COURSE_ID |       DATE |
-------------------------------------
|    user1 |        22 | 2013-06-03 |
|    user1 |        54 | 2013-06-03 |
|    user2 |        71 | 2013-06-04 |

这篇关于Mysql仅从最新日期中选择不同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！