create table #a (pp varchar(10),bo varchar(10),result varchar(10),result_se varchar(10))
insert into #a(pp,bo,result) values('YNB01','4039','Y')
insert into #a(pp,bo,result) values('YNB01','4039','N')
insert into #a(pp,bo,result) values('YNB02','4040','N')
insert into #a(pp,bo,result) values('YNB02','4040','N')
insert into #a(pp,bo,result) values('YNB03','4041','Y')
insert into #a(pp,bo,result) values('YNB03','4041','Y')
insert into #a(pp,bo,result) values('YNB04','4042','Y')
insert into #a(pp,bo,result) values('YNB05','4043','N')用pp 和 bo分组,如果result 的值全部部为'Y',则result_se='Y',全部为'N',则result_se='N',有Y和N则result_se='N'
能否用简易的语句编写,谢谢!
insert into #a(pp,bo,result) values('YNB01','4039','Y')
insert into #a(pp,bo,result) values('YNB01','4039','N')
insert into #a(pp,bo,result) values('YNB02','4040','N')
insert into #a(pp,bo,result) values('YNB02','4040','N')
insert into #a(pp,bo,result) values('YNB03','4041','Y')
insert into #a(pp,bo,result) values('YNB03','4041','Y')
insert into #a(pp,bo,result) values('YNB04','4042','Y')
insert into #a(pp,bo,result) values('YNB05','4043','N')用pp 和 bo分组,如果result 的值全部部为'Y',则result_se='Y',全部为'N',则result_se='N',有Y和N则result_se='N'
能否用简易的语句编写,谢谢!
select pp,bo,result,
result_re=case when exists(select 1 from #a where pp=t.pp and bo=t.bo and result_se='N'
then 'N'
else 'Y'
end
from #a t
select pp,bo,result,
result_re=case when exists(select 1 from #a where pp=t.pp and bo=t.bo and result='N')
then 'N'
else 'Y'
end
from #a t/**
pp bo result result_re
---------- ---------- ---------- ---------
YNB01 4039 Y N
YNB01 4039 N N
YNB02 4040 N N
YNB02 4040 N N
YNB03 4041 Y Y
YNB03 4041 Y Y
YNB04 4042 Y Y
YNB05 4043 N N(8 行受影响)
**/
insert into #a(pp,bo,result) values('YNB01','4039','Y')
insert into #a(pp,bo,result) values('YNB01','4039','N')
insert into #a(pp,bo,result) values('YNB02','4040','N')
insert into #a(pp,bo,result) values('YNB02','4040','N')
insert into #a(pp,bo,result) values('YNB03','4041','Y')
insert into #a(pp,bo,result) values('YNB03','4041','Y')
insert into #a(pp,bo,result) values('YNB04','4042','Y')
insert into #a(pp,bo,result) values('YNB05','4043','N')--select * from #a
--drop table #a
select
pp,bo,result,
case when exists(select 1 from #a where pp=t.pp and bo=t.bo and result='N') then 'N' else 'Y' end as result_re
from
#a t
/*pp bo result result_re
---------- ---------- ---------- ---------
YNB01 4039 Y N
YNB01 4039 N N
YNB02 4040 N N
YNB02 4040 N N
YNB03 4041 Y Y
YNB03 4041 Y Y
YNB04 4042 Y Y
YNB05 4043 N N(所影响的行数为 8 行)*/
case when exists(select 1 from #a where pp=t.pp and bo=t.bo and result='N')
then 'N' else 'Y' end as result_re
from #a t