表a是一个个人简历 表,有如下字段:身份号,姓名,职务,起始时间
select 身份号,姓名,职务,起始时间 from a group by 身份号,姓名,职务,起始时间对表进行分组,同时对起始时间进行排序,选取每一组'起始时间'最大的一条记录..也就是找出每个人最后的任职经历请问sql语句怎么写..谢谢@
select 身份号,姓名,职务,起始时间 from a group by 身份号,姓名,职务,起始时间对表进行分组,同时对起始时间进行排序,选取每一组'起始时间'最大的一条记录..也就是找出每个人最后的任职经历请问sql语句怎么写..谢谢@
SELECT newsid,title,classid FROM news a
WHERE (SELECT count(*) FROM news b WHERE b.classid=a.classid AND b.newsID>a.newsID)<5
ORDER BY classid desc,newsID desc
select 身份号,姓名,职务,起始时间,row_number()over(partition by 身份号,姓名,职务,起始时间 order by 起始时间 desc) rn from a group by 身份号,姓名,职务,起始时间
) where rn =1
select 身份号,姓名,职务,max(起始时间) from a group by 身份号,姓名,职务;
where not exists(select 1 from a where 身份号=t.身份号 and 起始时间>t.起始时间)
select 123 as 身份证号,'2009-10-02' as 时间 from dual
union all
select 123,'2011-10-02' from dual
union all
select 223,'2009-10-02' from dual
union all
select 223,'2003-10-02' from dual
union all
select 323,'2009-11-12' from dual
union all
select 323,'2009-10-02' from dual
)
select * from cte t
where not exists(select * from cte where 身份证号=t.身份证号 and 时间>t.时间)
身份证å 时间
---------------323 2009-11-12
223 2009-10-02
123 2011-10-02
--你就是要去某人的起始时间最大的那一条记录?
select 身份号,姓名,职务,起始时间 from a t1
where not exists(select null from a t2
where t2.身份号=t1.身份号 and t2.姓名=t1.姓名
and t2.职务=t1.职务 and t2.起始时间>t1.起始时间)
from (select 身份号,姓名,职务,起始时间,
row_number() over(partition by 身份号,姓名,职务 order by 起始时间 desc) rn
from a
)
where rn=1
select 身份号,姓名,职务,起始时间
from (select 身份号,姓名,职务,起始时间,
row_number() over(partition by 身份号 order by 起始时间 desc) rn
from a
)
where rn=1
--首先你这里要确认个问题:就是你的每个人的职务是一样的吗?
--会不会有这么个情况:一个人做过多份工作,每份工作担任的职务都不一样?
--如果是这个样子,那么你就不能用职务来进行group by了
--------------------------------------------------------------------------
--使用分析函数来取
with tab as
(
select 10000001 sid, 'Tom' name, 2 works, '201001' startDate from dual
union all
select 10000001 sid, 'Tom' name, 1 works, '201002' startDate from dual
union all
select 10000002 sid, 'Jack' name, 3 works, '201001' startDate from dual
union all
select 10000002 sid, 'Jack' name, 0 works, '201010' startDate from dual
)
select sid,name,works,startDate from (select sid,name,works,startDate, row_number() over(partition by sid,name order by startDate desc) rt from tab) where rt = 1
-------------------
SID NAME WORKS STARTDATE
10000001 Tom 1 201002
10000002 Jack 0 201010
where a.起始时间=(select max(起始时间) from where 身份号=a.身份号)
order by 起始时间
where a.起始时间=(select max(起始时间) from where 身份号=a.身份号)
FROM A T1,
(SELECT T3.身份号, MAX(T3.起始时间) 起始时间
FROM A T3
GROUP BY T3.身份号) T2
WHERE T1.身份号 = T2.身份号
AND T1.起始时间 = T2.起始时间;
可能看起来比较长,但原理很简单,就是构造一张最后时间表!测试过,60w数据0.2s!
select 123 as 身份证号,'2009-10-02' as 时间 from dual
union all
select 123,'2011-10-02' from dual
union all
select 223,'2009-10-02' from dual
union all
select 223,'2003-10-02' from dual
union all
select 323,'2009-11-12' from dual
union all
select 323,'2009-10-02' from dual
)
select * from cte t
where not exists(select * from cte where 身份证号=t.身份证号 and 时间>t.时间)