Table如下:
FILE_NAME STATUS
FTP_000006 E
FTP_000006 S
FTP_000006 S
FTP_000007 S
FTP_000007 S
FTP_000007 S
FTP_000008 S
FTP_000008 S
FTP_000008 S
需要select出status全是‘S’的文件名(注意,“status全是S”是重点:比如FTP_000006就不符合,因为它有一个STATUS是E,而不是S)。
此sql搜索出来的结果集应该如下:
FTP_000007
FTP_000008
select DISTINCT FILE_NAME from ttA a where not exists(select 1 from ttA where a.FILE_NAME=FILE_NAME and STATUS<>'S')
select distinct FILE_NAME from Table
where FILE_NAME not in (
select FILE_NAME from Table where STATUS <> 'S'
)
select distinct FILE_NAME from Table
where FILE_NAME not in (
select FILE_NAME from Table where STATUS <> 'S' or STATUS is null
)
select distinct FILE_NAME from Table A
where not exists (
select 1 from Table B where A.FILE_NAME = B.FILE_NAME and B.STATUS <> 'S' or B.STATUS is null
)
select DISTINCT T.*
from TEST T
where T.STATUS = 'S'
and not exists (select 1
from TEST T1
where T1.STATUS <> 'S'
and T.FILE_NAME = T1.FILE_NAME);