如何在此表中找到重复的连续值？

Say I have a table which I query like so:

假设我有一个表,我这样查询:

select date, value from mytable order by date

and this gives me results:

这给了我结果:

date                  value
02/26/2009 14:03:39   1                
02/26/2009 14:10:52   2          (a)
02/26/2009 14:27:49   2          (b)
02/26/2009 14:34:33   3
02/26/2009 14:48:29   2          (c)
02/26/2009 14:55:17   3
02/26/2009 14:59:28   4

I'm interested in the rows of this result set where the value is the same as the one in the previous or next row, like row b which has value=2 the same as row a. I don't care about rows like row c which has value=2 but does not come directly after a row with value=2. How can I query the table to give me all rows like a and b only? This is on Oracle, if it matters.

我对此结果集的行感兴趣,其中值与上一行或下一行中的值相同,如行b,其值= 2与行a相同。我不关心像行c这样的行,它有值= 2但不是直接在值为2的行之后。我怎样才能查询表格,只给出a和b之类的所有行?如果重要的话,这是在Oracle上。

3 个解决方案

#1

Use the lead and lag analytic functions.

使用超前和滞后分析功能。

create table t3 (d number, v number);
insert into t3(d, v) values(1, 1);
insert into t3(d, v) values(2, 2);
insert into t3(d, v) values(3, 2);
insert into t3(d, v) values(4, 3);
insert into t3(d, v) values(5, 2);
insert into t3(d, v) values(6, 3);
insert into t3(d, v) values(7, 4);

select d, v, case when v in (prev, next) then '*' end match, prev, next from (
  select
    d,
    v,
    lag(v, 1) over (order by d) prev,
    lead(v, 1) over (order by d) next
  from
    t3
)
order by
  d
;

Matching neighbours are marked with * in the match column,

匹配的邻居在匹配列中标有*,

alt text http://i28.tinypic.com/2drrojt.png

#2

This is a simplified version of @Bob Jarvis' answer, the main difference being the use of just one subquery instead of four,

这是@Bob Jarvis的答案的简化版本,主要区别在于只使用了一个子查询而不是四个,

with f as (select row_number() over(order by d) rn, d, v from t3)
select
  a.d, a.v,
  case when a.v in (prev.v, next.v) then '*' end match
from
  f a
    left join
  f prev
    on a.rn = prev.rn + 1
    left join
  f next
    on a.rn = next.rn - 1
order by a.d
;

#3

As @Janek Bogucki has pointed out LEAD and LAG are probably the easiest way to accomplish this - but just for fun let's try to do it by using only basic join operations:

正如@Janek Bogucki指出的那样,LEAD和LAG可能是实现这一目标的最简单方法 - 但只是为了好玩,让我们尝试只使用基本的连接操作:

SELECT mydate, VALUE FROM
  (SELECT a.mydate, a.value,
          CASE WHEN a.value = b.value THEN '*' ELSE NULL END AS flag1,
          CASE WHEN a.value = c.value THEN '*' ELSE NULL END AS flag2
     FROM
       (SELECT ROWNUM AS outer_rownum, mydate, VALUE
         FROM mytable
         ORDER BY mydate) a
     LEFT OUTER JOIN
       (select ROWNUM-1 AS inner_rownum, mydate, VALUE
         from mytable
         order by myDATE) b
       ON b.inner_rownum = a.outer_rownum
     LEFT OUTER JOIN
       (select ROWNUM+1 AS inner_rownum, mydate, VALUE
         from mytable
         order by myDATE) c
       ON c.inner_rownum = a.outer_rownum
     ORDER BY a.mydate)
  WHERE flag1 = '*' OR
        flag2 = '*';

Share and enjoy.

分享和享受。

#1