如下两个数据库,分别是 出国数据库 和 回国数据库出国数据库
证件号码 出国日期
001 20160207
004 20151111
003 20160730
001 20150101
001 20150308
001 20150901
002 20160101
002 20160728
003 20150202
回国数据库
证件号码 回国日期
001 20160217
004 20151113
003 20160815
001 20150120
001 20150309
001 20151001
002 20160801
002 20160110
003 20150209
现在希望 查询出
1. 所有出境行程超过1周的人员。
2. 所有出境行程没有超过一周的人员。好久没做数据库了,搞了半天没搞出来。
证件号码 出国日期
001 20160207
004 20151111
003 20160730
001 20150101
001 20150308
001 20150901
002 20160101
002 20160728
003 20150202
回国数据库
证件号码 回国日期
001 20160217
004 20151113
003 20160815
001 20150120
001 20150309
001 20151001
002 20160801
002 20160110
003 20150209
现在希望 查询出
1. 所有出境行程超过1周的人员。
2. 所有出境行程没有超过一周的人员。好久没做数据库了,搞了半天没搞出来。
from (select zjh,cgrq,row_number() over (partition by zjh order by cgrq asc) xh from cgsjk) a
left join (select zjh,hgrq,row_number() over (partition by zjh order by hgrq asc) xh from hgsjk) b on a.zjh=b.zjh and a.xh=b.xh
瞎写的,不懂对不对
(select '001' as card_id,'20160207' as t_date from dual union all
select '004','20151111' from dual union all
select '003','20160730' from dual union all
select '001','20150101' from dual union all
select '001','20150308' from dual union all
select '001','20150901' from dual union all
select '002','20160101' from dual union all
select '002','20160728' from dual union all
select '003','20150202' from dual )
,t1 as
(select '001' as card_id,'20160217' as t_date from dual union all
select '004','20151113' from dual union all
select '003','20160815' from dual union all
select '001','20150120' from dual union all
select '001','20150309' from dual union all
select '001','20151001' from dual union all
select '002','20160801' from dual union all
select '002','20160110' from dual union all
select '003','20150209' from dual)
SELECT A.CARD_ID, A.T_DATE AS "出国时间", T1.T_DATE AS "回国时间",
TO_DATE(T1.T_DATE, 'YYYY-MM-DD') - TO_DATE(A.T_DATE, 'YYYY-MM-DD') + 1 AS "出国时间" -- 算头不算尾
FROM (SELECT CARD_ID, T_DATE,
NVL(LEAD(T_DATE) OVER(PARTITION BY CARD_ID ORDER BY T_DATE),
'9999-12-31') AS LAST_DATE
FROM T) A
LEFT JOIN T1
ON T1.CARD_ID = A.CARD_ID
AND T1.T_DATE >= A.T_DATE
AND T1.T_DATE < A.LAST_DATE
ORDER BY A.CARD_ID, A.T_DATE
with t1 as
(select '001' as hm,'20160207' as cgrq from dual union all
select '004','20151111' from dual union all
select '003','20160730' from dual union all
select '001','20150101' from dual union all
select '001','20150308' from dual union all
select '001','20150901' from dual union all
select '002','20160101' from dual union all
select '002','20160728' from dual union all
select '003','20150202' from dual ),
t2 as
(select '001' as hm,'20160217' as hgrq from dual union all
select '004','20151113' from dual union all
select '003','20160815' from dual union all
select '001','20150120' from dual union all
select '001','20150309' from dual union all
select '001','20151001' from dual union all
select '002','20160801' from dual union all
select '002','20160110' from dual union all
select '003','20150209' from dual),
h1 as (select t1.*,row_number() over (partition by hm order by cgrq) rn from t1),
h2 as (select t2.*,row_number() over (partition by hm order by hgrq) rn from t2)
select h1.hm,h1.cgrq,h2.hgrq from h1 left join h2 on (h1.hm=h2.hm and h1.rn=h2.rn) order by 1,2;
select h.time-c.time day from c,h where c.id = h.id and day >7;