100280 1 EL 25729 IN
100280 1 EL 25729 OUT
100380 1 EL 22613 OUT
100380 1 EL 733 IN
100489 1 EL 28606 OUT
100489 1 EL 20843 IN 表里有这么5列
现在达到的目的就是第1-3列相同,第四列不同 第5列 是out,in 这样的顺序 的数据抽出来也就是说 得到的结果 :100380 1 EL 22613 OUT
100380 1 EL 733 IN
100489 1 EL 28606 OUT
100489 1 EL 20843 IN 第1,2条不要
100280 1 EL 25729 OUT
100380 1 EL 22613 OUT
100380 1 EL 733 IN
100489 1 EL 28606 OUT
100489 1 EL 20843 IN 表里有这么5列
现在达到的目的就是第1-3列相同,第四列不同 第5列 是out,in 这样的顺序 的数据抽出来也就是说 得到的结果 :100380 1 EL 22613 OUT
100380 1 EL 733 IN
100489 1 EL 28606 OUT
100489 1 EL 20843 IN 第1,2条不要
out in这样的顺序 这个顺序仅凭rownum很不严谨
而且比如这3天,
100380 1 EL 22613 OUT
100380 1 EL 733 IN
100489 1 EL 28606 OUT
如何确定这是out-->in 还是 in-->out
实际数据比这个要负责,规则无法确定
首先取出来的数据前三列必须一样,第四列不一样 第5列是OUT IN 这样的排序 的前提是
后面有个时间列 order by 这个时间 得出来的结果是out in 这样的数据取出来
with temp as(
select '100280' empid,1 A,'EL' B,25729 C,'IN' D from dual
union all
select '100280' empid,1 A,'EL' B,25729 C,'OUT' D from dual
union all
select '100380' empid,1 A,'EL' B,22613 C,'OUT' D from dual
union all
select '100380' empid,1 A,'EL' B,733 C,'IN' D from dual
union all
select '100489' empid,1 A,'EL' B,28606 C,'OUT' D from dual
union all
select '100489' empid,1 A,'EL' B,20843 C,'IN' D from dual
)
select empid,A,B,C,D from temp t3 where t3.empid in (
select t1.empid from temp t1,temp t2 where t1.empid = t2.empid and t1.D = 'IN' and t2.D = 'OUT' and t1.C < t2.C
) order by empid,D desc
with tt as(
select '100280' col1, 1 col2, 'EL' col3, 25729 col4,'IN' state, date'2010-01-01' time from dual union all
select '100280' col1, 1 col2, 'EL' col3, 25729 col4,'OUT' state, date'2010-01-03' time from dual union all
select '100380' col1, 1 col2, 'EL' col3, 22613 col4,'OUT' state, date'2010-01-04' time from dual union all
select '100380' col1, 1 col2, 'EL' col3, 733 col4,'IN' state, date'2010-01-05' time from dual union all
select '100489' col1, 1 col2, 'EL' col3, 28606 col4,'OUT' state, date'2010-01-06' time from dual union all
select '100489' col1, 1 col2, 'EL' col3, 20843 col4,'IN' state, date'2010-01-07' time from dual)
SELECT *
FROM (SELECT tt.* FROM tt ORDER BY TIME)
WHERE NOT (LEVEL = 1 AND connect_by_isleaf = 1)
START WITH TIME = (SELECT MIN(TIME) FROM test WHERE state = 'OUT')
CONNECT BY PRIOR TIME < TIME AND
PRIOR decode(state, 'OUT', 1, -1) = -1 * decode(state, 'OUT', 1, -1) AND
PRIOR col1 = col1 AND
PRIOR col2 = col2 AND
PRIOR col3 = col3 AND
PRIOR col4 <> col4
一句select搞不出来 可以换存储过程。
select '100280' empid,1 A,'EL' B,25729 C,'IN' D from dual
union all
select '100280' empid,1 A,'EL' B,25729 C,'OUT' D from dual
union all
select '100380' empid,1 A,'EL' B,22613 C,'OUT' D from dual
union all
select '100380' empid,1 A,'EL' B,733 C,'IN' D from dual
union all
select '100489' empid,1 A,'EL' B,28606 C,'OUT' D from dual
union all
select '100489' empid,1 A,'EL' B,20843 C,'IN' D from dual
)
select t3.empid,t3.A,t3.B,t3.C,t3.D from(
select row_number() over(order by t1.empid) rn1,t1.* from temp t1) t3,
(select row_number() over(order by t2.empid) rn2,t2.* from temp t2) t4
where t3.empid = t4.empid and t3.D ='OUT' and t4.D ='IN' and rn1 < rn2