SQL取每个分类的前几名的sql语句写法

表table
字段 title,hits,boardid

取每boardid个hits前两位的title值

oracle写法

select title,dense_rank() over(partition by boardid order by hits desc) rn
from topic t
where rn<3

access写法，只能取第一位的值

select b.title,b.hits,b.boardid
from
(select max(hits) as hit ,boardid from dv_topic group by boardid)
as t
left join dv_topic as b on b.hits=t.hit and b.boardid=t.boardid

sql server写法，取每类前两位，access可依样画葫芦，把text改成别的即可

select tt.boardid,tt.title,tt.hits as fistHit,(select top 1 hits from text where text.boardid=tt.boardid and text.hits<tt.hits order by hits desc) as secondHit,
(select top 1 title from text where text.boardid=tt.boardid and text.hits<tt.hits order by hits desc) as secondtitle from
(
select text.title,text.hits,text.boardid
from
(
select max(hits) as hits ,boardid from text group by boardid
)
as t
left join text on text.hits=t.hits and text.boardid=t.boardid
)
as tt

相关阅读

• rs.open sql,conn 与conn.execute(sql)的区别