表数据如下:
班号 参观人数 参观时间
class_no visit_num visit_time
0901 10 2008-12-01
0902 35 2008-12-01
0902 40 2008-12-03
0901 18 2008-12-11
0901 21 2008-12-31
0903 30 2008-12-31要求统计 【12月最后访问时间】的班级参观人数:就是说,0901班,12月最后访问时间是 2008-12-31,0901 最后参观人数是 21
0902班,12月最后访问时间是 2008-12-03,0902 最后参观人数是 40
0903班,12月最后访问时间是 2008-12-31,0902 最后参观人数是 30
能否用一条sql,显示如下:0902 40 2008-12-03
0901 21 2008-12-31
0903 30 2008-12-31
SELECT class_no , visit_num , visit_time FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY class_no ORDER BY visit_time DESC ) AS RN,T.* FROM YOURTABLE T)
WHERE RN =1
A.CLASS_NO,A.VISIT_NUM,B.VISIT_TIME
FROM
(SELECT CLASS_NO,MAX(VISIT_TIME) AS VISIT_TIME FROM A GROUP BY CLASS_NO)
AS B
LEFT JOIN A ON A.CLASS_NO=B.CLASS_NO AND A.VISIT_TIME=B.VISIT_TIME
ORDER BY B.VISIT_TIME
Oracle不知道行不行
WHERE
RN=1