现有两个表A和表B,表A中每条记录对应表B中“最多”4条记录,也可能是1-4条记录(最少1条)。现在是想写个SQL语句查出表A和表B中的记录的关联。结果要求是表A中每条记录,表B中均显示4条记录,如果基础表对应的记录少于4条,则补足空行显示4条。
举例如下:
表A:
FA1 FA2
1 …
2 …
4 …
……表B:
FB1 FB2 FB3
1 A …
1 B …
2 A …
4 A …
4 B …
4 C …
4 D …
……
以上记录查询结果应如下所示:
FA1 FB2
1 A
1 B
1
1
2 A
4 A
4 B
4 C
4 D
举例如下:
表A:
FA1 FA2
1 …
2 …
4 …
……表B:
FB1 FB2 FB3
1 A …
1 B …
2 A …
4 A …
4 B …
4 C …
4 D …
……
以上记录查询结果应如下所示:
FA1 FB2
1 A
1 B
1
1
2 A
4 A
4 B
4 C
4 D
from (
select * from tab_A
union all
select * from tab_A
union all
select * from tab_A
union all
select * from tab_A
) A,tab_B B
where A.FA1=B.FB1(+)
select A.FA1,B.FB2
from (
select * from tab_A
union all
select * from tab_A
union all
select * from tab_A
union all
select * from tab_A
) A,tab_B B
where A.FA1=B.FB1(+)这个好像不对吧! fa1=4 会出现16条记录 , 一条语句不会写,写个过程可以
select * from tab_A
union all
select * from tab_A 两个全表扫描 加 一个union all 效率就看你的表多大了
from (
select * from tab_A
union all
select * from tab_A
union all
select * from tab_A
union all
select * from tab_A
) A,tab_B B
where A.FA1=B.FB1(+)应该是有问题的,union all 得到的纪录确实是原来表A的所有记录都变成4条,但是做了左连接之后,四条记录中每一条都会和B表做外连接
2 from (
3 select 1 as rid,a.* from a
4 union all
5 select 2,a.* from a
6 union all
7 select 3,a.* from a
8 union all
9 select 4,a.* from a
10 ) A, (select fb1, fb2, fb3, row_number()over(partition by fb1 order by fb2,
fb3) as rid from b )B
11 where A.FA1=B.FB1(+)
12 and a.rid = b.rid(+)
13 /FA1 FB2
---------- ----------
1 A
1 B
1
1
2 A
2
2
2
4 A
4 B
4 C
4 D已选择12行。