查詢每組資料中最新月份的記錄

舟之橋發表於2013-01-17

id year month
111 2012 1
111 2012 2
111 2013 1
112 2012 1
112 2012 2
112 2012 3
112 2012 4

注：每一組（id，year，month） 都是唯一的，我現在要 查詢出 

(111,2013,1)

(112,2012,4)

SELECT a.*
FROM A a,
(
SELECT a.id, max(b.year) as year
, max(a.month) AS month 
FROM A a,
(
SELECT c.id
, max(c.year) year 
FROM A c
GROUP BY c.payItemId
) b
WHERE a.id=b.id AND a.year=b.year
GROUP BY a.id
) b
WHERE a.id = b.id AND a.year=b.year AND a.month = b.month


用了3層巢狀才查出來，不知有沒有簡便的方法，歡迎各位大神指正

相關文章