SQL Server 2017

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

CREATE TABLE A (
A1 NVARCHAR(10),  -- 公司代號
A2 NVARCHAR(10)); -- 車號

INSERT INTO A
VALUES
('1','A'),
('1','B'),
('1','C'),
('2','D'),
('2','E'),
('3','F'),
('4','H');

SELECT *
FROM A;


A1
A2


1
A

1
B

1
C

2
D

2
E

3
F

4
H


CREATE TABLE B (
B1 NVARCHAR(10),  -- 異動單號
B2 NVARCHAR(10)); -- 車號

INSERT INTO B
VALUES
('a1','A'),
('a2','A'),
('a3','A'),
('a4','A'),
('a5','B'),
('a6','C'),
('a7','C'),
('a8','C');

SELECT *
FROM B;


B1
B2


a1
A

a2
A

a3
A

a4
A

a5
B

a6
C

a7
C

a8
C


CREATE TABLE C (
C1 NVARCHAR(10),  -- 異動單號
C2 NVARCHAR(10)); -- 異動理由

INSERT INTO C
VALUES
('a1',N'轉換給1'),
('a2',N'轉換給2'),
('a3',N'轉換給3'),
('a4',N'退租'),
('a5',N'轉換給1'),
('a6',N'轉換給2'),
('a7',N'轉換給3'),
('a8',N'轉換給4');

SELECT *
FROM C;


C1
C2


a1
轉換給1

a2
轉換給2

a3
轉換給3

a4
退租

a5
轉換給1

a6
轉換給2

a7
轉換給3

a8
轉換給4


SELECT A1 AS'公司代號',A2 AS '車號',C2 AS '異動理由'
FROM A
LEFT JOIN (
SELECT *,ROW_NUMBER() OVER (PARTITION BY B.B2 ORDER BY B1 DESC) AS ROWNUM
FROM B
) AS D ON D.B2=A.A2 AND D.ROWNUM=1
LEFT JOIN C ON C.C1=D.B1
ORDER BY CONVERT(INT,A1),A2


公司代號
車號
異動理由


1
A
退租

1
B
轉換給1

1
C
轉換給4

2
D
null

2
E
null

3
F
null

4
H
null

C1	C2
a1	轉換給1
a2	轉換給2
a3	轉換給3
a4	退租
a5	轉換給1
a6	轉換給2
a7	轉換給3
a8	轉換給4