多表联合查询 技巧问题啊

Question

表一：
A_ID, A_NAME
  1    A
  2    B
  3    C
  4    D
  5    E
  6    F
表二：
B_ID, A_ID
  1    3
  2    2
表三：
C_ID, A_ID
  1    1
  2    1
  3    4
表四：
D_ID, A_ID
  1    5
  2    3
  3    3
  4    3要求查询结果：
A_ID  A_NAME  B_ID  C_ID   D_ID
  1      A           1
  1      A           2
  2      B      2
  3      C      1
  3      C                  2
  3      C                  3
  3      C                  4
  4      D           3
  5      E                  1
  6      F就是一个统计，高分奖励

Answer 1

SELECT * FROM A LEFT NATURAL JOIN B  LEFT NATURAL JOIN C  LEFT NATURAL JOIN D

Answer 2

select a1.a_id,b1.b_id,c1.c_id,d1.d_id
from a1,b1,c1,d1
where a1.a_id = b1.a_id(+)
and a1.a_id = c1.a_id(+)
and a1.a_id = d1.a_id(+)
order by a1.a_id;

Answer 3

看看你这个 LEFT NATURAL JOIN 对吗。

Answer 4

SELECT a.a_id,b.b_id,c.c_id,d.d_id  FROM A 
 LEFT JOIN B on a.a_id=b.a_id
 LEFT  JOIN C  on a.a_id=c.a_id
 LEFT JOIN D on a.a_id=d.d_name order by a.a_id这样应该能出来

Answer 5

没看错的话，是不是如果匹配，那么不允许三表交叉，而是每个表单独一行？
简单的外连接确实取不出来。LEFT NATURAL JOIN不是外连接，NATURAL LEFT JOIN才是
SELECT A_ID, A_NAME, B_ID, NULL C_ID, NULL D_ID
FROM   A NATURAL JOIN   B
UNION ALL
SELECT A_ID, A_NAME, NULL B_ID, C_ID, NULL D_ID
FROM   A NATURAL JOIN   C
UNION ALL
SELECT A_ID, A_NAME, NULL B_ID, NULL C_ID, D_ID
FROM   A NATURAL JOIN   D
UNION ALL
SELECT A_ID, A_NAME, NULL B_ID, NULL C_ID, NULL D_ID
FROM   A 
NATURAL LEFT JOIN B 
NATURAL LEFT JOIN C 
NATURAL LEFT JOIN D
WHERE  b_id IS NULL
AND    c_id IS NULL
AND    d_id IS NULL当然如果是insert语句的话，要简单一些

Answer 6

select tt.a_id,tt.a_name,t.b_id,t.c_id,t.d_id from(  
select a.a_id ,a.a_name,b.b_id ,null c_id,null d_id from a left join b on a.a_id=b.a_id
union
select a.a_id ,a.a_name,null,c.c_id ,null from a left join c on a.a_id=c.a_id
union
select a.a_id ,a.a_name,null ,null,d.d_id from a left join d on a.a_id=d.a_id)t
right join a tt
on tt.a_id=t.a_id
and not( t.b_id is null and t.c_id is null and t.d_id is null)
order by 1

Answer 7

--应该按照2楼的方法可以出来相应的结果，可在10g下跑出来查询结果不对，只好用下面的方法： 
SQL> SELECT A.A_ID, A.A_NAME, B_ID, C_ID, D_ID
  2    FROM TEST_A A,
  3         (SELECT A_ID, B_ID, NULL C_ID, NULL D_ID FROM TEST_B B
  4          UNION ALL
  5          SELECT A_ID, NULL, C_ID, NULL FROM TEST_C C
  6          UNION ALL
  7          SELECT A_ID, NULL, NULL, D_ID FROM TEST_D D)BCD
  8   WHERE A.A_ID = BCD.A_ID(+)
  9   ORDER BY 1;
 
      A_ID A_NAME       B_ID       C_ID       D_ID
---------- ------ ---------- ---------- ----------
         1 A                          1 
         1 A                          2 
         2 B               2            
         3 C               1            
         3 C                                     3
         3 C                                     4
         3 C                                     2
         4 D                          3 
         5 E                                     1
         6 F                            
 
10 rows selected

Answer 8

10楼 、11楼才靠谱   union all

Answer 9

给一个比较龌龊的，呵呵。
(select a.A_ID,a.A_NAME,b.B_ID,null,null from a right join b on a.A_ID=b.A_ID
union
select a.A_ID,a.A_NAME,null,c.c_ID,null from a right join c on a.A_ID=c.A_ID
union
select a.A_ID,a.A_NAME,null,null,d.d_ID from a right join d on a.A_ID=d.A_ID)
union 
select  a.A_ID,a.A_NAME,NULL,null,null from a where a.A_ID=6

Answer 10

再加大点难度哦：要求查询结果： （注意A_ID为3的记录）
A_ID  A_NAME  B_ID  C_ID  D_ID 
  1      A          1 
  1      A          2 
  2      B      2 
  3      C      1           2
  3      C                  3 
  3      C                  4 
  4      D          3 
  5      E                  1 
  6      F 压缩几行，好比是：
A_ID  A_NAME  B_ID  C_ID  D_ID 
  1      A     1     
  1      A     2     
  1      A     3 
  1      A          1 
  1      A          2 
压缩成：
A_ID  A_NAME  B_ID  C_ID  D_ID 
  1      A     1    1 
  1      A     2    2 
  1      A     3 

Answer 11

分组就能实现了
a_id相同的所有记录都压缩？

Answer 12

不是所有相同的a_id压缩成一行，是看后面 b_id、c_id、d_id，以最多的记录为限。

Answer 13

以第一个问题的结果作为基础
select a_id,max(a_name),max(b_id),max(c_id),max(d_id) from(
select a_id,a_name,b_id,c_id,d_id,row_number()over(partition by a_id,
  case when case when cb>=cc and cb>=cd then b_id 
    when cc>=cd and cc>=cb then c_id
    else d_id end is not null then 1 end order by rownum)rn from(
select a_id,a_name,b_id,c_id,d_id,count(b_id)over(partition by a_id)cb,
  count(c_id)over(partition by a_id)cc,
  count(d_id)over(partition by a_id)cd from a_view))
group by a_id,rn  
order by a_idA_ID MAX(A_NAME) MAX(B_ID) MAX(C_ID) MAX(D_ID)
1 a 1
1 a 2
2 b 2
3 c 1 2
3 c 4
3 c 3
4 d 3
5 e 1
6 f

Answer 14

这个应该更正确点
select a_id,max(a_name)a_name,max(b_id)b_id,max(c_id)c_id,max(d_id)d_id from( 
select case when a.a_id is null and b.a_id is null then c.a_id 
  when a.a_id is null then b.a_id else a.a_id end a_id,
  case when a.a_name is null and b.a_name is null then c.a_name
  when a.a_name is null then b.a_name
  else a.a_name end a_name,a.b_id,b.c_id,c.d_id,
  case when a.rn is null and b.rn is null then c.rn 
  when a.rn is null then b.rn else a.rn end rn
from (select a_id,a_name,b_id,
  row_number()over(partition by a_id order by rownum)
   rn from a_view where b_id is not null)a 
  full join (select a_id,a_name,c_id,
    row_number()over(partition by a_id order by rownum) rn 
    from a_view where c_id is not null)b
  on a.a_id=b.a_id and a.rn=b.rn full join
  (select a_id,a_name,d_id,row_number()over(
    partition by a_id order by rownum)rn
     from a_view where d_id is not null)c
  on case when a.a_id is null then b.a_id else a.a_id end=c.a_id
  and case when a.rn is null then b.rn else a.rn end=c.rn)
group by a_id,rn
union all
select * from a_view where b_id is null and c_id is null and d_id is null
order by a_idA_ID A_NAME B_ID C_ID D_ID
1 a 1
1 a 2
2 b 2
3 c 1 2
3 c 3
3 c 4
4 d 3
5 e 1
6 f

Answer 15

select aid,aname,max(bid),max(cid),max(did)
from (
      select a1.a_id aid,a1.a_name aname,bid,cid,did,rn
        from (
              select a1.a_id aid,b1.b_id bid,null cid,null did,rank()over(partition by a1.a_id order by b1.b_id) rn
              from a1,b1
              where a1.a_id = b1.a_id
              union all
              select a1.a_id,null bid,c1.c_id cid,null did,rank()over(partition by a1.a_id order by c1.c_id) rn
              from a1,c1
              where a1.a_id = c1.a_id
              union all
              select a1.a_id,null bid,null cid,d1.d_id did,rank()over(partition by a1.a_id order by d1.d_id) rn
              from a1,d1
              where a1.a_id = d1.a_id) m,a1
        where a1.a_id = m.aid(+)
     )
group by aid,aname,rn
order by aid;

Answer 16

SELECT a.a_id,b.b_id,c.c_id,d.d_id  FROM A 
LEFT JOIN B on a.a_id=b.a_id 
LEFT  JOIN C  on a.a_id=c.a_id 
LEFT JOIN D on a.a_id=d.d_name order by a.a_id 

Answer 17

你的语句再加个 order by a_id 才是正确结果，但是看看你的执行计划，cost是39啊，注意效率