SQL Server 2017

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

CREATE TABLE A (
Accs_num varchar(40) NULL ,
body varchar(40) NULL ,
Cookies varchar(40) NULL);

INSERT INTO A (Accs_num,body,Cookies)
VALUES 
('09xx','26578','lca234-'),
(null,'98323','2ba'),
('0966','34389','3DE'),
-- 額外加入
('0967','123','8KPPP'),
('0968','123','8KPPP'),
('0969',null,'8KPPP'),
('0970','123',null),
('0971',null,null),
('0972',null,null),
('0973','456','8KPPP');

SELECT *
FROM A;


Accs_num
body
Cookies


09xx
26578
lca234-

null
98323
2ba

0966
34389
3DE

0967
123
8KPPP

0968
123
8KPPP

0969
null
8KPPP

0970
123
null

0971
null
null

0972
null
null

0973
456
8KPPP


CREATE TABLE B (
Accs_num varchar(40) NULL ,
recommendl varchar(40) NULL);

INSERT INTO B (Accs_num,recommendl)
VALUES ('09xx','AAA'),
('2ba','BBB'),
('0966','CCC'),
('3DE','DDD'),
-- 額外加入
('8KPPP','EEE'),
('0972','GGG');

SELECT *
FROM B;


Accs_num
recommendl


09xx
AAA

2ba
BBB

0966
CCC

3DE
DDD

8KPPP
EEE

0972
GGG


SELECT DISTINCT B.Accs_num,C.body,B.recommendl
FROM (
SELECT A.body,ISNULL(B.Accs_num,A.Cookies) AS NewAccs_num
FROM A
LEFT JOIN B ON A.Accs_num=B.Accs_num
) AS C
INNER JOIN B ON C.NewAccs_num=B.Accs_num
ORDER BY B.recommendl


Accs_num
body
recommendl


09xx
26578
AAA

2ba
98323
BBB

0966
34389
CCC

8KPPP
null
EEE

8KPPP
123
EEE

8KPPP
456
EEE

0972
null
GGG

Accs_num	body	Cookies
09xx	26578	lca234-
null	98323	2ba
0966	34389	3DE
0967	123	8KPPP
0968	123	8KPPP
0969	null	8KPPP
0970	123	null
0971	null	null
0972	null	null
0973	456	8KPPP