兩條或兩條以上的數據﹐只要status有一條記錄為N﹐那么結果就為N﹐例如﹕
狀況1﹕
name status
A Y
A N想得出結果是﹕A N
狀況2﹕
name status
A Y
A Y想得出結果是﹕A Y
狀況3﹕
name status
A N
A N
想得出結果是﹕A N
狀況1﹕
name status
A Y
A N想得出結果是﹕A N
狀況2﹕
name status
A Y
A Y想得出結果是﹕A Y
狀況3﹕
name status
A N
A N
想得出結果是﹕A N
from [Table]
group by name
declare @tb table(name char(1),status char(1));
insert into @tb
select 'A','Y' union all select 'A','N' union all
select 'B','N' union all select 'B','N' union all
select 'C','Y' union all select 'C','Y';
select name,
(case when exists(select * from @tb where status='N' and name=t.name)
then 'N' else 'Y' end) status
from @tb t
group by name;
/*
A N
B N
C Y
*/
insert into tb
select 'A','Y' union all select 'A','N' union all
select 'B','N' union all select 'B','N' union all
select 'C','Y' union all select 'C','Y';
go
select * from tb
/*
name status
---- ------
A Y
A N
B N
B N
C Y
C Y(所影响的行数为 6 行)
*/select distinct name , (case when exists (select 1 from tb where name = t.name and status = 'N') then 'N' else 'Y' end) status from tb t/*
name status
---- ------
A N
B N
C Y(所影响的行数为 3 行)*/drop table tb