TA表 如下:
ID TYPE NUM
----------------
45 A 8
46 B 6
47 A 10
48 C 5
49 A 9
50 B 10
51 B 12
52 B 3
想要结果
ID TYPE NUM
----------------
47 A 10
51 B 12
48 C 5
最终要想取到ID 47 51 48PS :小弟尝试了下面的方法,但无奈会多取得ID=50这个结果,求SQL大大支招
select id from ta
where num in (select max(num)
from ta group by type)
ID TYPE NUM
----------------
45 A 8
46 B 6
47 A 10
48 C 5
49 A 9
50 B 10
51 B 12
52 B 3
想要结果
ID TYPE NUM
----------------
47 A 10
51 B 12
48 C 5
最终要想取到ID 47 51 48PS :小弟尝试了下面的方法,但无奈会多取得ID=50这个结果,求SQL大大支招
select id from ta
where num in (select max(num)
from ta group by type)
select ID,TYPE,NUM
from (select ID,TYPE,NUM,row_number() over(partition by type order by num desc) rn
from tb)
where rn=1
要取type=A or B or C并且num处于中间的?
select id,TYPE,num,Row_Number()over(PARTITION BY TYPE ORDER BY num desc) nr from ta
)
WHERE rn=1;
取各个type中num最大值记录的 id
from
(select type,
max(num) max_num
from TA
group by type
) TB,
TA
where TA.type = TB.type
and TA.num = TB.max_num
select id from ta a
where num = (select Max(num)
from ta WHERE a.TYPE=type )
select id from ta a
where not exixts ( select 1 from ta b where a.type=b.type and a.num<b.num)
select a.ID,b.TYPE,b.NUM
from TA a inner join
(select TYPE,max(NUM) as NUM from TA group by TYPE) b
on a.TYPE = b.TYPE and a.NUM = b.NUM;