有一个表:
A B C
1 20101126190803 0
1 20101126190823 1
1 20101126190843 1
1 20101126190903 1
1 20101126190923 1
1 20101126190943 1
1 20101126191003 0
1 20101126191023 0
2 20101126191043 1
2 20101126191103 1
3 20101126191123 1
3 20101126191143 1
3 20101126191203 0
2 20101126191223 1
2 20101126191243 1
将列A和C在一段时间内重复的,只显示B列时间最早的记录求教
A B C
1 20101126190803 0
1 20101126190823 1
1 20101126190843 1
1 20101126190903 1
1 20101126190923 1
1 20101126190943 1
1 20101126191003 0
1 20101126191023 0
2 20101126191043 1
2 20101126191103 1
3 20101126191123 1
3 20101126191143 1
3 20101126191203 0
2 20101126191223 1
2 20101126191243 1
将列A和C在一段时间内重复的,只显示B列时间最早的记录求教
1 20101126190803 0
1 20101126190823 1
1 20101126191003 0
2 20101126191043 1
3 20101126191203 0
2 20101126191223 1
---------- -------------- ----------
1 20090420101625 0
2 20090420101633 1
3 20090420101641 0
1 20090420101705 0已用时间: 00: 00: 00.03
sys@ORCLselect a,b,c
2 from (select a,to_char(b,'yyyymmddhh24miss') b,c,row_number() over(partition by a,c order by b)
recno from tttt) where recno=1; A B C
---------- -------------- ----------
1 20090420101625 0
2 20090420101633 1
3 20090420101641 0
A B C
1 20101126190803 0 保留
1 20101126190823 1 保留
1 20101126190843 1 不要
1 20101126190903 1 不要
1 20101126190923 1 不要
1 20101126190943 1 不要
1 20101126191003 0 保留(注意这里的A列还是1)
1 20101126191023 0 不要
最后结果可能就是:
A B C
1 20101126190803 0
1 20101126190903 1
1 20101126200903 0
1 20101126210903 1
1 20101126220903 0求教了~~
select a,b,c
from (select a,b,c,lag(c,1,99) over (order by b) d from table order by a,b)
where c<>d;
(SELECT A,B,C,ROW_NUMBER() OVER(PARTITION BY A,C ORDER BY B RN FROM table)
)
WHERE RN = 1
lag(c,1,0) over(
2 order by b) c1 from tttt; A B C A1 C1
---------- -------------- ---------- ---------- ----------
1 20101126190803 0 0 0
1 20101126190823 1 1 0
1 20101126190843 1 1 1
1 20101126190903 1 1 1
1 20101126190923 1 1 1
1 20101126190943 1 1 1
1 20101126191003 0 1 1
1 20101126191023 0 1 0已选择8行。sys@ORCL>select a,b,c
2 from ( select a,to_char(b,'yyyymmddhh24miss') b,c,lag(a,1,0) over(order by b) a1,lag(c,1,0) ove
r(
3 order by b) c1 from tttt) where a1<>a or c1<>c; A B C
---------- -------------- ----------
1 20101126190803 0
1 20101126190823 1
1 20101126191003 0
FROM
(
SELECT a,TO_CHAR(b,'yyyymmddhh24miss')b,c,
lead(a,1,0) over(ORDER BY b) a1,
lead(c,1,3) over(ORDER BY b) c1 FROM tttt
)WHERE a1<>a OR c1<>c多谢zxf_feng