常常在SQL中需要取某個欄位的最大日期中最大的序號資料，目前筆者想到2種方式分享給大家!
最近看到系統中有要「取出員工最大CHANGE_DATE中的最大序號」，如下圖所示，
範例如下，
--建立暫存的資料表
CREATE TABLE #t1
(
EMP_ID VARCHAR(10)
, CHANGE_DATE DATETIME
, SEQ_NO INT
);
--建立測試資料
INSERT INTO #t1 ( EMP_ID, CHANGE_DATE, SEQ_NO )
VALUES ('001', '2013-08-01', 1);
--要取 002, 2013-08-01, 2
INSERT INTO #t1 ( EMP_ID, CHANGE_DATE, SEQ_NO )
VALUES ('001', '2013-08-01', 2);
INSERT INTO #t1 ( EMP_ID, CHANGE_DATE, SEQ_NO )
VALUES ('002', '2013-08-01', 1);
INSERT INTO #t1 ( EMP_ID, CHANGE_DATE, SEQ_NO )
VALUES ('002', '2013-08-01', 2);
INSERT INTO #t1 ( EMP_ID, CHANGE_DATE, SEQ_NO )
VALUES ('002', '2013-08-01', 3);
--要取 002, 2013-08-02, 1
INSERT INTO #t1 ( EMP_ID, CHANGE_DATE, SEQ_NO )
VALUES ('002', '2013-08-02', 1);
--要取 002, 2013-08-01, 2
INSERT INTO #t1 ( EMP_ID, CHANGE_DATE, SEQ_NO )
VALUES ('003', '2013-08-01', 1);
SELECT * FROM #t1 ;
方法1:使用日期*100+序來加總來比較
SELECT *
FROM #t1 A
WHERE CAST(CONVERT(VARCHAR, A.CHANGE_DATE, 112) AS INT) * 100 + A.SEQ_NO
= ( SELECT MAX(CAST(CONVERT(VARCHAR, B.CHANGE_DATE, 112) AS INT) * 100 + B.SEQ_NO )
FROM #t1 B WHERE A.EMP_ID = B.EMP_ID );
方法2:使用 CTE + ROW_NUMBER() 取等於1的資料
WITH MAX_ORDER AS (
SELECT *
, row_num = ROW_NUMBER() OVER(PARTITION BY EMP_ID ORDER BY CHANGE_DATE DESC, SEQ_NO DESC)
FROM #t1 A)
SELECT EMP_ID, CHANGE_DATE, SEQ_NO
FROM MAX_ORDER
WHERE row_num = 1;
如果大家有其他的做法，煩請跟我分享一下，謝謝大家!
2013/08/26 補上rex大大提供的方式
方法3:rex大大提供的方式，先取出最大的CHANGE_DATE後，再取出對應最大的SEQ_NO
SELECT a.EMP_ID ,
a.CHANGE_DATE ,
MAX(SEQ_NO)
FROM #t1 a
JOIN ( SELECT EMP_ID ,
MAX(CHANGE_DATE) ad
FROM #t1
GROUP BY EMP_ID
) b ON a.EMP_ID = b.EMP_ID
AND a.CHANGE_DATE = b.ad
GROUP BY a.EMP_ID ,
a.CHANGE_DATE非常感謝rex大大的分享!
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
