问个SQL语句，一时想不出来，求帮助
表中字段a,b
模拟记录为
001 3
001 4
001 5
002 4
002 5
003 2
003 6
003 9
要求结果为
001 5
002 5
003 9
也就是要取每个a字段中的最大的b字段的值
[解决办法]
漏了from
;with tb as
(select 001 as a,3 as b
union all select 001,4
union all select 001,5
union all select 002,4
union all select 002,5
union all select 003,2
union all select 003,6
union all select 003,9
)
select a,b
from (select *,ROW_NUMBER() Over(partition by A order by b desc) as rn from tb)t
where rn=1
/*
15
25
39
*/
--或者
select *
from tb a
where b in (select MAX(b) from tb b where a.a=b.a)
[解决办法]
--另另一种写法
SELECT * FROM TB M
WHERE NOT EXISTS
(
SELECT 1
FROM TB N
WHERE M.a = N.a
AND N.b > M.b
)
