SQL笛卡尔类型联接 [英] SQL Cartesian type join
本文介绍了SQL笛卡尔类型联接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有2个表(此示例已简化),我想以特定的方式进行联接.
I have 2 tables (simplified for this example) which I want to join in a particular way.
- 表1(ae)具有主题编号,ae术语
- 表2(mh)具有主题编号,mh术语
这里有帮助的构架
INSERT INTO ae VALUES ('0001-0012','DRY EYE (12 OCT 2017)');
INSERT INTO ae VALUES ('0001-0012', 'DEPRESSION (7 OCT 2017)');
INSERT INTO ae VALUES ('0001-0013','SKIN ATROPHY, LEFT UPPER ARM (4 OCT 2017 )');
INSERT INTO mh VALUES ('0001-0012', 'DIABETES MELLITUS (UN JUL 2007)');
INSERT INTO mh VALUES ('0001-0012', 'GASTRO-ESOPHAGEAL REFLUX INTERMITENT GR1 (18 AUG 2017)');
INSERT INTO mh VALUES ('0001-0012', 'ESOPHAGITIS GR 1 (18 AUG 2017)');
INSERT INTO mh VALUES ('0001-0012', 'DIARRHEA INITERMITTENT GR2 (5 JUL 2017 )');
INSERT INTO mh VALUES ('0001-0012', 'FATIGUE INTERMITTENT GR1 (18 AUG 2017)');
INSERT INTO mh VALUES ('0001-0013', 'VOMITING, INTERMITTENT GR1 (6 JUL 2017 )');
我希望我的输出看起来像这样:
I want my output to look like this:
有什么帮助吗?如您所见,它是我所需要的粗糙的加入!
Any help out there? As you can see its a gnarly join I need!
推荐答案
您可以使用row_number()
和full join
:
select coalesce(ae.col1, mh.col1) as col1, ae.col2, mh.col2
from (select ae.*, row_number() over (partition by col1 order by col1) as seqnum
from ae
) ae full join
(select mh.*, row_number() over (partition by col1 order by col1) as seqnum
from mh
) mh
on mh.col1 = ae.col1 and mh.seqnum = ae.seqnum
order by coalesce(ae.col1, mh.col1), seqnum;
这篇关于SQL笛卡尔类型联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文