则工号21010001在该部门的最后记录为2016-1-31,21010002在该部门最后一条记录为空,请问该如何写脚本?以下为建表语句
create table test1(
code varchar2(100),
startdate date,
enddate date,
org varchar2(100))
insert into test1 values (21010001, date '2014-10-1', date '2014-12-31','广东有限公司人力资源部');
insert into test1 values (21010001, date '2015-01-01', date '2016-01-31','广东有限公司人力资源部');
insert into test1 values (21010001, date '2016-02-01', null,'广东有限公司政企客户部');
select * from test1
insert into test1 values (21010002, date '2014-01-01', date '2014-06-30','广东有限公司人力资源部');
insert into test1 values (21010002, date '2014-07-01', null,'广东有限公司人力资源部');
4 个解决方案
#1
-- 这个意思 ?
with m as (
select row_number() over(partition by code order by startdate desc) rn, t.*
from test1 t
where org = '广东有限公司人力资源部'
)
select * from m where rn =1 ;
#2
版主的写法就可以。。
#3
SELECT * FROM (SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY A.CODE,A.ORG ORDER BY NVL(A.ENDDATE,TO_DATE('9999/12/31','YYYY/MM/DD')) DESC) RN
FROM TEST1 A) B
WHERE B.RN = 1;
ROW_NUMBER() OVER(PARTITION BY A.CODE,A.ORG ORDER BY NVL(A.ENDDATE,TO_DATE('9999/12/31','YYYY/MM/DD')) DESC) RN
FROM TEST1 A) B
WHERE B.RN = 1;
#4
SELECT * FROM (SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY A.CODE,A.ORG ORDER BY NVL(A.ENDDATE,TO_DATE('9999/12/31','YYYY/MM/DD')) DESC) RN
FROM TEST1 A) B
WHERE B.RN = 1;
#1
-- 这个意思 ?
with m as (
select row_number() over(partition by code order by startdate desc) rn, t.*
from test1 t
where org = '广东有限公司人力资源部'
)
select * from m where rn =1 ;
#2
版主的写法就可以。。
#3
SELECT * FROM (SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY A.CODE,A.ORG ORDER BY NVL(A.ENDDATE,TO_DATE('9999/12/31','YYYY/MM/DD')) DESC) RN
FROM TEST1 A) B
WHERE B.RN = 1;
ROW_NUMBER() OVER(PARTITION BY A.CODE,A.ORG ORDER BY NVL(A.ENDDATE,TO_DATE('9999/12/31','YYYY/MM/DD')) DESC) RN
FROM TEST1 A) B
WHERE B.RN = 1;
#4
SELECT * FROM (SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY A.CODE,A.ORG ORDER BY NVL(A.ENDDATE,TO_DATE('9999/12/31','YYYY/MM/DD')) DESC) RN
FROM TEST1 A) B
WHERE B.RN = 1;