SQL选择条件

2787184

我有两个表temp_number和temp_port，我只想选择那些只有端口名是'ip sub'的数字，并且要排除那些既有端口名又有端口名是'local loop'的数字。

    temp_number
    -----------------------------------
    numberid  | name
    -----------------------------------
    1         | abc
    2         | def
    3         | ghi
    -----------------------------------  

    temp_port
    -----------------------------------
    portid    | numberid  | name
    -----------------------------------
    1         | 1         |  local loop
    2         | 1         |  ip sub
    3         | 2         |  local loop
    4         | 3         |  ip sub
    -----------------------------------  



    CREATE TABLE temp_number  
    ( numberid number(10),  --pk
      name varchar2(50));  

     CREATE TABLE temp_port  
    ( portid number(10),     --pk 
      numberid number(10),   --fk
      name varchar2(50));  

    insert into temp_number values(1,'abc');
    insert into temp_port values(1,1,'local loop');
    insert into temp_port values(2,1,'ip sub');
    insert into temp_number values(2,'def');
    insert into temp_port values(3,2,'local loop');
    insert into temp_number values(3,'ghi');
    insert into temp_port values(4,3,'ip sub');

What I tried : 
select n.name, p.name from temp_number n, temp_port paving 
where n.numberid=p.numberid  and p.name not in ('local loop');

    actual result:
    -----------------------------------
    name | Name
    -----------------------------------
    abc  | ip sub
    ghi  | ip sub


    expected result:
    -----------------------------------
    name | Name
    -----------------------------------
    ghi  | ip sub

â€œXmoůŜ

试试这个过滤

SELECT DISTINCT t.name, p.name
FROM temp_number t, temp_ports p
Where t.numberid=p.numberid AND t.numberid NOT IN
    (SELECT s.numberid FROM temp_port s WHERE s.name = 'local loop')

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。