Ask a SQL statement?

tongc3000

Table A:
id ratey
001 Y
001 N
002 Y
002 Y
003 N
003 N
Table B:
id ratey
001 N
002 Y
003 N
How to use a SQL statement to get table B (grouped by id, as long as a certain ratey is ‘N’, the corresponding ratey=’N’ for this id)? Urgent!

我是杀人狂

select top 1 * from tablename group by id order by ratey

我是杀人狂

declare @a table(id char(10),ratey char(10))
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
result:
id ratey
---------- ----------
001 N
002 Y
003 N

(The number of rows affected is 3 rows)

菜鸟明星

insert into @t
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 @t

select 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