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');

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


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

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

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


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


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

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

發電機
2006-03-10
2006-03-15
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 (
SELECT FA,FB,FC,FD
FROM TableX
UNION ALL
SELECT FA,FB,FC,FD
FROM TableY) AS TempK
) AS TempM
WHERE ROWNUM = 1
ORDER BY FA DESC


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


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

發電機
2006-03-10
2006-03-15
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