SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TableX (
FA Nvarchar(20) NULL ,     -- 品名
FB Nvarchar(20) NULL,      -- 開始日期
FC Nvarchar(20) NULL,      -- 完成日期
FD Nvarchar(20) NULL);     -- 階段

INSERT INTO TableX (FA,FB,FC,FD)
VALUES 
(N'配電盤','2006-03-01','2006-03-15','1'),
(N'配電盤','2006-03-15','2006-03-18','2'),
(N'配電盤','2006-03-18','2006-03-20','3'),
(N'發電機','2006-03-05','2006-03-10','1'),
(N'發電機','2006-03-10','2006-03-12','2');

SELECT FA AS '品名',FB AS '開始日期',FC AS '完成日期', FD AS '階段'
FROM TableX;


品名
開始日期
完成日期
階段


配電盤
2006-03-01
2006-03-15
1

配電盤
2006-03-15
2006-03-18
2

配電盤
2006-03-18
2006-03-20
3

發電機
2006-03-05
2006-03-10
1

發電機
2006-03-10
2006-03-12
2


-- 方法一
SELECT FA AS '品名',FB AS '開始日期',FC AS '完成日期', FD AS '階段'
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY FA ORDER BY FD DESC) AS ROWNUM,*
FROM TableX) AS TempM
WHERE ROWNUM = 1
ORDER BY FA DESC


品名
開始日期
完成日期
階段


配電盤
2006-03-18
2006-03-20
3

發電機
2006-03-10
2006-03-12
2


-- 方法二
SELECT TableX.FA AS '品名',TableX.FB AS '開始日期',TableX.FC AS '完成日期', TableX.FD AS '階段'
FROM TableX
INNER JOIN (
SELECT FA,MAX(FD) AS FD
FROM TableX
GROUP BY FA) AS Temp ON Temp.FA=TableX.FA AND Temp.FD=TableX.FD
ORDER BY TableX.FA DESC


品名
開始日期
完成日期
階段


配電盤
2006-03-18
2006-03-20
3

發電機
2006-03-10
2006-03-12
2

品名	開始日期	完成日期	階段
配電盤	2006-03-01	2006-03-15	1
配電盤	2006-03-15	2006-03-18	2
配電盤	2006-03-18	2006-03-20	3
發電機	2006-03-05	2006-03-10	1
發電機	2006-03-10	2006-03-12	2