如何选择具有最大日期的行 [英] How to Select row with maximum date

问题描述

这是我的表格名称tblPE

this is my table name tblPE

PEID |idnum  | PE_DATE_EXAM   | ATTENDANCE       
1    | 39    | 2014-08-01     | PRESENT       
2    | 42    | 2014-08-10     | ABSENT            
3    | 39    | 2014-08-12     | PRESENT           
4    | 43    | 2014-08-05     | PRESENT        
5    | 42    | 2014-07-15     | NULL           
6    | 39    | 2014-07-03     | ABSENT       
7    | 41    | 2014-08-01     | PRESENT    

我想选择最大PE_DATE_EXAM值，其中idnum = 39且ATTENDANCE = PReSENT

i want to select the maximum PE_DATE_EXAM value where idnum = 39 and ATTENDANCE=PReSENT

结果应为:

PEID |idnum  | PE_DATE_EXAM   | ATTENDANCE
-------------------------------------------
3    | 39    | 2014-08-12     | PRESENT

推荐答案

对于单个记录返回，可以使用LIMIT:

For a single record return you can use LIMIT :

SELECT * FROM tblPE t
WHERE t.idnum = 39 AND t.ATTENDANCE = 'PRESENT'
ORDER BY t.PE_DATE_EXAM DESC
LIMIT 1;

如果希望所有idnum都动态显示，则可以使用NOT EXISTS():

If you want it dynamic for all idnum's , you can use NOT EXISTS() :

SELECT * FROM tblPE t
WHERE NOT EXISTS(SELECT 1 FROM tblPE s
                 WHERE t.idnum = s.idnum AND s.ATTENDANCE = 'PRESENT')
  AND t.attendance = 'PRESENT'

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