34,590
社区成员
发帖
与我相关
我的任务
分享
if object_id('tb')is not NULL
drop table tb
go
create table tb(id varchar(100),settle_id varchar(100),status varchar(100),pay_amt varchar(100))
insert into tb
select '01','001','S','500' union
select '02','001','S','100' union
select '03','001','N','200' union
select '04','002','S','900' union
select '05','002','S','200'
--select * from tb
SELECT * FROM tb a WHERE status='S'
AND NOT EXISTS(SELECT 1 FROM tb WHERE settle_id=a.settle_id AND status<>a.status)
/*
id settle_id status pay_amt
04 002 S 900
05 002 S 200
*/
ELECT * FROM [tb] AS t WHERE [status]='S'
AND NOT EXISTS(SELECT 1 FROM tb WHERE [settle_id]=t.[settle_id] AND [status]!=t.[status])