>我有一个基表,我想再连接 2 个表来获取我需要的所有数据。在运行查询时,我得到了一个笛卡尔乘积,因为其中 2 个表(主表和我正在连接的另一个表(正在通过non-unique
键连接。
下面是一个示例:
发票 (i(
id order_id name comment
-----------------------------------------------------------------
1 500 Males Mice
2 500 Females Mice
3 500 Courier Fee Within City
订单 (o(
id order_number
------------------------------
500 AN5246516264
订单项目 (oi(
id order_id strain_id species_id comments
-----------------------------------------------------------------
1100 500 858 9876 Mice
1101 500 858 9876 Mice
1102 500 NULL NULL Within City
使用上面定义的表,这是我的查询:
查询
SELECT
i.name, i.comment,
o.order_number,
oi.strain_id, oi.species_id
FROM invoice i
LEFT JOIN order o
ON i.order_id = o.id
LEFT JOIN order_items oi
ON o.id = oi.order_id
运行查询后,我得到一个笛卡尔乘积,如下所示(不一定按顺序(:
name comment order_number strain_id species_id
-----------------------------------------------------------------------
Males Mice AN5246516264 858 9876 ---> I want this row
Males Mice AN5246516264 858 9876
Males Within City AN5246516264 NULL NULL
Females Mice AN5246516264 858 9876
Females Mice AN5246516264 858 9876 ---> I want this row
Females Within City AN5246516264 NULL NULL
Courier Fee Mice AN5246516264 858 9876
Courier Fee Mice AN5246516264 858 9876
Courier Fee Within City AN5246516264 NULL NULL ---> I want this row
我了解这里发生的事情,我知道为什么它会产生笛卡尔乘积,但我不知道如何解决我的问题。
我只想将order_number
、strain_id
和species_id
附加到Invoice (i)
表中。
是的,表结构以一种奇怪的方式构建。我确实相信数据库设计师在制作表格时喝醉了,但我现在无法改变这一点。
有什么建议吗?
编辑
我已经查看了所有 3 个表,但找不到任何其他列来连接它们。我只剩下order_id
.
您可以先使用函数 row_number()
对表中的行进行编号i
和oi
,然后将其用作连接条件的一部分:
with i as (select row_number() over (partition by order_id order by id) rn, i.*
from invoice i),
oi as (select row_number() over (partition by order_id order by id) rn, oi.*
from order_items oi)
select i.name, i.comments, o.order_number, oi.strain_id, oi.species_id
from i left join orders o on i.order_id = o.id
left join oi on oi.order_id = o.id and oi.rn = i.rn
测试数据和输出:
create table invoice (id number(4), order_id number(4),
name varchar2(15), comments varchar2(20));
insert into invoice values (1, 500, 'Males', 'Mice');
insert into invoice values (2, 500, 'Females' ,'Mice');
insert into invoice values (3, 500, 'Courier Fee', 'Within City');
--
create table orders (id number(4), order_number varchar2(15));
insert into orders values (500, 'AN5246516264');
--
create table order_items(id number(5), order_id number(4),
strain_id number(5), species_id number(5), comments varchar2(20));
insert into order_items values(1100, 500, 858, 9876, 'Mice');
insert into order_items values(1101, 500, 858, 9876, 'Mice');
insert into order_items values(1102, 500, NULL, NULL, 'Within City');
输出:
NAME COMMENTS ORDER_NUMBER STRAIN_ID SPECIES_ID
--------------- -------------------- --------------- --------- ----------
Males Mice AN5246516264 858 9876
Females Mice AN5246516264 858 9876
Courier Fee Within City AN5246516264