sql - 自联接 Oracle SQL

Question

我有这张桌子：

    CREATE TABLE ASSETS 
(
  AID INTEGER DEFAULT  ON NULL assets_id_seq.nextval 
, ANAME VARCHAR2(30) 
, ATYPE VARCHAR2(20) 
, SUB_ID NUMBER 
, CONSTRAINT ASSETS_PK PRIMARY KEY 
  (
    AID 
  )
  ENABLE 
);

我插入了几个值，如下所示：

aid aname                   atype       subscriber_phone
1   superfast internet 8    adsl        42504556
4   line                    fixed line  42504556
5   superfast internet 32   adsl        42551344
6   line                    fixed line  42551344
7   superfast internet 50   adsl        49111222
8   line                    fixed line  49111222
9   line                    fixed line  49000000
10  line                    fixed line  49555333
11  line                    fixed line  49000323
12  line                    fixed line  49000131 

我想将每个subscriber_phone 和他们的名字都记录下来，就像这样：

subscriber_phone  aname_adsl                  aname_fixed_line
42504556          superfast internet 8        line  
42551344          superfast internet 32       line      
49111222          superfast internet 50       line    
49000000          null                        line
49555333          null                        line
49000323          null                        line
49000131          null                        line

我所做的是自我加入：

select a.sub_id,a.aname,b.aname 
from 
    assets a, assets b 
where 
    a.sub_id = b.sub_id and a.atype <> b.atype 

这就是我得到的：

subscriber_phone  aname                  aname1
42504556          line                   superfast internet 8
42504556          superfast internet 8   line
42551344          line                   superfast internet 32
42551344          superfast internet 32  line
49111222          line                   superfast internet 50
49111222          superfast internet 50  line

有什么帮助吗？

标签： sqloraclejoin

Answer 1

您可以使用枢轴而不是自联接：

select sub_id, adsl, fixed_line
from (
  select aname, atype, sub_id
  from assets
  where atype in ('adsl', 'fixed line')
)
pivot (
  max(aname) for (atype) in ('adsl' as adsl, 'fixed line' as fixed_line)
) p
order by sub_id;

    SUB_ID ADSL                           FIXED_LINE                    
---------- ------------------------------ ------------------------------
  42504556 superfast internet 8           line                          
  42551344 superfast internet 32          line                          
  49000000                                line                          
  49000131                                line                          
  49000323                                line                          
  49111222 superfast internet 50          line                          
  49555333                                line                          

7 rows selected. 

db<>小提琴

如果您真的想自连接，那么您可以使用子查询来获取不同的类型，然后进行完全外连接：

select coalesce(a.sub_id, b.sub_id) as sub_id, a.aname, b.aname 
from (
  select sub_id, aname
  from assets
  where atype = 'adsl'
) a
full outer join (
  select sub_id, aname
  from assets
  where atype = 'fixed line'
) b
on a.sub_id = b.sub_id
order by sub_id;

    SUB_ID ANAME                          ANAME                         
---------- ------------------------------ ------------------------------
  42504556 superfast internet 8           line                          
  42551344 superfast internet 32          line                          
  49000000                                line                          
  49000131                                line                          
  49000323                                line                          
  49111222 superfast internet 50          line                          
  49555333                                line                          

7 rows selected. 

db<>小提琴

...但是枢轴更干净，如果需要，以后更容易扩展到更多类型/列。

如果您不能在没有固定线路的情况下使用 ADSL，那么它会更简单一些 - 您不需要完整的外部连接或子查询：

select b.sub_id, a.aname, b.aname
from assets b
left join assets a
on a.sub_id = b.sub_id
and a.atype = 'adsl'
where b.atype = 'fixed line'
order by sub_id;

    SUB_ID ANAME                          ANAME                         
---------- ------------------------------ ------------------------------
  42504556 superfast internet 8           line                          
  42551344 superfast internet 32          line                          
  49000000                                line                          
  49000131                                line                          
  49000323                                line                          
  49111222 superfast internet 50          line                          
  49555333                                line                          

7 rows selected.