TABLE(NO,code,sumary,seq) 在相同的NO下會有不同的code,sumary,seq EX: NO code sumary seq 94041501 1111 TEST1 1 94041501 2113 TEST2 2 94041501 2110 TEST3 3 94041502 1111 TESTA 1 94041502 2113 TESTB 2 94041503 2110 TESTI 1 請問要如何select出 code 有2113 但是沒有2110 的NO 以例子來說就是找出94041502 我只想到比較慢的寫法 select no from table where no not in (select distinct no from table where code='2110') 不知道有沒有其他寫法?

hi 您好.    如果是寫這樣的話

 select no from table where no not in (select distinct no from table where code='2110')

那麼 如果資料庫資料如下 94041501 1111 TEST1 1 94041501 2113 TEST2 2 94041501 2110 TEST3 3 94041502 1111 TESTA 1 94041502 2113 TESTB 2 94041503 1111 TESTA 1 會同時select出 94041502 與 94041503 這兩筆 但 94041503 並不包含2113 如下的SQL應該可以獲得正確結果

select distinct no from table 
where no not in (select distinct no from table where code='2110')
and code='2113'

hope helpful.

kartan_01你好:    這樣理論上會快一點.... 但是你的NO, CODE要有INDEX才會快    

SELECT A.NO FROM 
(   
  SELECT DISTINCT NO FROM TABLE WHERE CODE = '2113'
) A,
(
  SELECT DISTINCT NO FROM TABLE WHERE CODE = '2110'
) B
WHERE A.NO = B.NO( )
AND B.NO IS NULL

Sorry !! 插一下花    也可以這樣寫

select  distinct
        no
from    table t
where   exists (select  *
                from    table t1
                where   t1.no = t.no
                and     t1.code = '2113')
and     not exists (select  *
                    from    table t2
                    where   t2.no = t.no
                    and     t2.code = '2110')

---------------------------------- 小弟才疏學淺，若有謬誤尚請不吝指教 ----------------------------------

------
Fishman