自联接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

有什么帮助吗？

Answer 1

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

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<>fiddle

如果你真的想要自连接，那么你可以使用子查询来获得不同的类型，然后执行一个完整的外连接：

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<>fiddle

..。但pivot更清晰，而且更容易在以后需要的时候扩展到更多的类型/列。

如果你不能在没有固定线路的情况下使用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. 

Answer 2

或者：

SQL> select a.subscriber_phone,
  2    max(case when a.atype = 'adsl' then a.aname end) aname_adsl,
  3    max(case when a.atype = 'fixed line' then a.aname end) aname_fixed_line
  4  From test a
  5  group by a.subscriber_phone
  6  order by subscriber_phone;

SUBSCRIB ANAME_ADSL            ANAME_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.

SQL>

Answer 3

你可以使用自连接来做你想做的事情。看起来是这样的：

select coalesce(a_adsl.sub_id, a_fl.sub_id) as sub_id,
       a.aname as dsl, b.aname as fixed_line
from (select a_adsl.*
      from assets a_adsl
      where type = 'adsl'
     ) a_adsl full join
     (select a_fl.*
      from assets a_fl
      where type = 'fixed line'
     ) a_fl
     on a_adsl.sub_id = a_fl.sub_id ;

就我个人而言，我更喜欢聚合方法。但这似乎是您试图实现的内容。

实际上，您也可以使用一个left join和更少的子查询来做到这一点：

select a1.sub_id as sub_id,
       (case when a1.type = 'adsl' then a1.name
             when a2.type = 'adsl' then a2.name
        end) as adsl,
       (case when a1.type = 'fixed line' then a1.name
             when a2.type = 'fixed Line' then a2.name
        end) as fixed_line
from assets a1 left join
     assets a1
     on a1.sub_id = a2.sub_id and a1.name < a2.name;