declare @status char(1) set @status='R' select * from 表 where status=@status union all select * from 表 where item not in (select item from 表 where status=@status)
declare @status char(1) set @status='R' select * from 表 where status=@status union all select * from 表 where item not in (select item from 表 where status=@status)
select * from yourtable A where exists (select * from yourtable where item = A.item and status = A.status and status = 'R') or no exists (select * from yourtable where item = A.item and status = 'R')
set @status='R'
select * from 表 where status=@status union all select * from 表 where item not in (select item from 表 where status=@status)
set @status='R'
select * from 表 where status=@status union all select * from 表 where item not in (select item from 表 where status=@status)
where exists (select * from yourtable
where item = A.item and status = A.status and status = 'R')
or no exists (select * from yourtable where item = A.item and status = 'R')