sql 查询每个gcid的前3个
0
最佳答案
0
分组排序的问题啊：
select * from(
select gcid,name, row_number() over (partition by gcid
order by name desc) as rowno
from tel) A
where a.rowno<=3
奖励园豆：5
这个兼容性很差，而且性能不好，只有新的数据库支持。最好的办法是写存储过程，用临时表。
其他回答(2)
0
select * from table a where 2>(select count(id) from table b where a.gid<b.gid and a.gcid=b.gcid)
这个效率也不高，不过也是一种办法。
0
select * from (select gid,gcname,gcid,case when row_number() over(partition by gcid order by gcid asc) = 1 then 1
when row_number() over(partition by gcid order by gcid asc) = 2 then 2
when row_number() over(partition by gcid order by gcid asc) = 3 then 3
else 4 end as rnt
from tableA) as t
where t.rnt <= 3
