select a.rid as rid,u.username as username,a.rcontent as rcontent,a.rtime as rtime,d.dname as dname
from reports a
left join users u on (a.raid = u.userid)
left join dept d on (u.userdeptid = d.ddid)
where
(select count(*) from reports
where raid = a.raid and rtime > a.rtime ) < 3
and a.ravailable=1
order by a.rid,a.raid;
如图，有三张表联查，要根据username分组，查询出每个用户下按照时间排序，最新的三条记录。这个是不带参数的，而且没排序，要修改成带一个时间参数的脚本，需求为：当前时间下每个用户最新的三条记录。
------解决方案--------------------
select * from (select row_number() over (partition by u.username order by a.rtime )ronum,
a.rid as rid,u.username as username,a.rcontent as rcontent,a.rtime as rtime,d.dname as dname
from reports a
left join users u on (a.raid = u.userid)
left join dept d on (u.userdeptid = d.ddid) ) a where ronum<=3