MySQL选择记录的最新日期 [英] MySQL select of record with latest date

问题描述

我有两个表:COURSES和PERMISSIONS.我正在尝试做一个SELECT，它显示给定帐户中给定学生所有现有课程的最新权限值.

I have two tables: COURSES and PERMISSIONS. I'm trying to do a SELECT that shows the latest permission value for all existing courses of a given student, in a given account.

现在，我这样做:

SELECT COURSES.NAME, PERMISSIONS.VALUE, PERMISSIONS.TS
FROM COURSES LEFT JOIN PERMISSIONS
ON PERMISSIONS.C_ID = COURSES.C_ID AND PERMISSIONS.S_ID = '12345'
WHERE COURSES.A_ID = 'ABCDE'

我得到的结果是

NAME          |  VALUE  |   TS
-----------------------------------------------
Mathematics   | 1       | 2012-01-19 19:13:21
Mathematics   | 0       | 2012-01-19 19:13:15
Mathematics   | 0       | 2012-01-19 19:20:19
Mathematics   | 0       | 2012-01-19 19:20:27
Mathematics   | 0       | 2012-01-19 19:21:29
Biology       |         | 

我需要的是仅显示具有最新时间戳(TS)的记录，以便显示此内容:

What I need is to show only the record with the latest timestamp (TS) so it shows this:

NAME          |  VALUE  |   TS
-----------------------------------------------
Mathematics   | 0       | 2012-01-19 19:21:29
Biology       |         | 

我在课程名称上尝试了GROUP BY并在时间戳上尝试了ORDER BY，但是它没有获得正确的记录.

I tried GROUP BY on the course name in conjunction with an ORDER BY on the timestamp but it didn't pick up the correct record.

任何人都可以帮忙吗?

推荐答案

SELECT COURSES.NAME, PL.VALUE, PL.TS
FROM COURSES
LEFT JOIN 
  ( SELECT 
       PERMISSIONS.C_ID, PERMISSIONS.VALUE, PERMISSIONS.TS
    FROM PERMISSIONS
    JOIN
      ( SELECT P.C_ID, MAX(P.TS) AS LATEST
        FROM PERMISSIONS P
        WHERE P.S_ID = '12345'
        GROUP BY P.C_ID ) PG
    ON PERMISSIONS.TS = PG.LATEST ) PL
  ON PL.C_ID = COURSES.C_ID
WHERE COURSES.A_ID = 'ABCDE'

根据数据的大小，您可以考虑在(C_ID，TS)上为PERMISSIONS表添加索引，以加快查找最新日期的时间:

Depending on the size of your data you might consider adding an index on (C_ID,TS) for PERMISSIONS table to speed up finding latest date:

ALTER TABLE `PERMISSIONS` ADD INDEX (`C_ID`,`TS`);

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