例如:
a | b | c |
1 | 5 | abc |
2 | 6 | bcd |
1 | 7 | ade |
2 | 8 | adc |
若取按a列分组后,b列最大,的所有列的记录,
a | b | c |
1 | 6 | bcd |
2 | 8 | adc |
可以使用如下语句
select
*
from
test
where
b
in
(
select
max
(id)
from
test
group
by
a)
适用于所有数据库
select
t1.a,t1.b,t1.c
from test t1
inner join
(seelct a, max (b) as b from test group by a) t2
on t1.a = t2.a and t1.b = t2.b
from test t1
inner join
(seelct a, max (b) as b from test group by a) t2
on t1.a = t2.a and t1.b = t2.b
适用于所有数据库
select
a,b,c
from (
select a,b,c
,row_number() over (partition by a order by b desc ) rn
from test
)
where rn = 1
from (
select a,b,c
,row_number() over (partition by a order by b desc ) rn
from test
)
where rn = 1
适用于oracle