小弟新手求一查询方法,该怎么处理
小弟新手求一查询方法
表tbjob id,userid,jontime
表tbuser id,name
求 根据时间desc 去重 userid
不知道意思表达的清楚不
select name from tbuser where id in(select userid from tbjob group by userid having count(userid)>1 order by jontime desc)
------解决方案--------------------
表tbjob id,userid,jontime
表tbuser id,name
求 根据时间desc 去重 userid
不知道意思表达的清楚不
select name from tbuser where id in(select userid from tbjob group by userid having count(userid)>1 order by jontime desc)
------解决方案--------------------
- SQL code
create table tbjob (id int, userid varchar(5), jontime datetime) insert into tbjob select 1, '0001', '2012-1-1 00:12:02' union all select 2, '0001', '2012-1-1 10:12:12' union all select 3, '0002', '2012-1-1 11:12:12' union all select 4, '0002', '2012-1-2 12:12:12' union all select 5, '0003', '2012-3-4 01:12:12' with t as (select row_number() over(partition by userid order by jontime desc) rn, id,userid,jontime from tbjob ) select id,userid,jontime from t where rn=1 order by jontime desc id userid jontime ----------- ------ ----------------------- 5 0003 2012-03-04 01:12:12.000 4 0002 2012-01-02 12:12:12.000 2 0001 2012-01-01 10:12:12.000 (3 row(s) affected)