表A:
id ratey
001 Y
001 N
002 Y
002 Y
003 N
003 N
表B:
id ratey
001 N
002 Y
003 N
如何用一条SQL语句得到表B(按id分组,只要有某ratey为‘N’,则此id对应的ratey=’N’)?急!
id ratey
001 Y
001 N
002 Y
002 Y
003 N
003 N
表B:
id ratey
001 N
002 Y
003 N
如何用一条SQL语句得到表B(按id分组,只要有某ratey为‘N’,则此id对应的ratey=’N’)?急!
insert into @a select '001','Y' UNION ALL
SELECT '001','N' UNION ALL
SELECT '002','Y' UNION ALL
SELECT '002','Y' UNION ALL
SELECT '003','N' UNION ALL
SELECT '003','N'
select id,min(ratey) as ratey from @a group by id
结果:
id ratey
---------- ----------
001 N
002 Y
003 N (所影响的行数为 3 行)
select '001', 'Y'
union all
select '001', 'N'
union all
select '002', 'Y'
union all
select '002', 'Y'
union all
select '003', 'N'
union all
select '003', 'N'
select * from @tselect distinct id,ratey=case when (select count(1) from @t where id=t.id and ratey='N')>0 then 'N' else ratey end
from @t t