iT邦幫忙

0

SQL TeraDATA 如何join 完再join 後distinct

問題:
A、B Table 需要inner join
但希望先on A.Accs_num = B.Accs_num當join key

若查不到~
再用on A.Cookies = B.Accs_num 當join key

希望生成結果C Table,但是不能重複

https://ithelp.ithome.com.tw/upload/images/20201126/20127643FX6xe0RVCm.png

2 個回答

3
rogeryao
iT邦大師 6 級 ‧ 2020-11-26 20:45:44
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');
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 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

Demo

0
pojen
iT邦新手 1 級 ‧ 2020-11-28 10:53:11

感謝 rogeryao 提供表格生成 DML. 我的版本:

select case when accs_num is null then accs_num2 else accs_num end,body,recommendl
from (
select a.*,b.accs_num accs_num2,b.recommendl
from A
inner join B on a.Accs_num=b.Accs_num or a.Cookies=b.Accs_num
) xx
where accs_num=accs_num2
or accs_num is null;

https://ithelp.ithome.com.tw/upload/images/20201128/20033353Z0uME6giTp.png

我要發表回答

立即登入回答