SQL Server 2019

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

CREATE TABLE TableX
(ID varchar(20),
NO varchar(20),
DIA varchar(20));

INSERT INTO TableX 
VALUES
('20210101','A01','100'),
('20210101','A02','200'),
('20210102','A01','100'),
('20210103','A01','100'),
('20210103','A01','100');

SELECT *
FROM TableX;

CREATE TABLE TableA
(ID varchar(20),
NO varchar(20),
DIA varchar(20));

INSERT INTO TableA 
VALUES
('20210102','X01','100'),
('20210102','X02','150');

SELECT *
FROM TableA;


ID
NO
DIA


20210101
A01
100

20210101
A02
200

20210102
A01
100

20210103
A01
100

20210103
A01
100




ID
NO
DIA


20210102
X01
100

20210102
X02
150


-- 解法一
SELECT X.ID,X.NO,X.DIA
FROM TableX AS X
WHERE X.ID NOT IN (SELECT B.ID FROM TableA AS B)
UNION ALL
SELECT A.ID,A.NO,A.DIA
FROM TableA AS A
ORDER BY X.ID,X.NO


ID
NO
DIA


20210101
A01
100

20210101
A02
200

20210102
X01
100

20210102
X02
150

20210103
A01
100

20210103
A01
100


-- 解法二
SELECT A.ID,
CASE WHEN X.NO IS NOT NUll THEN X.NO ELSE A.NO END AS NO,
CASE WHEN X.DIA IS NOT NUll THEN X.DIA ELSE A.DIA END AS DIA
FROM TableX AS A
LEFT JOIN TableA AS X ON X.ID = A.ID


ID
NO
DIA


20210101
A01
100

20210101
A02
200

20210102
X01
100

20210102
X02
150

20210103
A01
100

20210103
A01
100

ID	NO	DIA
20210101	A01	100
20210101	A02	200
20210102	A01	100
20210103	A01	100
20210103	A01	100