SQL TeraDATA 如何join 完再join 後distinct

#sql

minislowdata 2020-11-26 18:14:38 ‧ 1380 瀏覽

分享至

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

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

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

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 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

回應
分享
檢舉

登入發表回應

pojen

iT邦研究生 5 級 ‧ 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;