如何在此表中找到重复的连续值? [英] How can I find duplicate consecutive values in this table?
本文介绍了如何在此表中找到重复的连续值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我有一个表格,我像这样查询:
Say I have a table which I query like so:
select date, value from mytable order by date
这给了我结果:
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
我对这个结果集的行感兴趣,其中值与前一行或下一行中的行相同,例如行 b 的 value=2 与行 a 相同.我不关心行 c 之类的行,它的值=2,但不直接出现在值=2 的行之后.如何查询表以仅提供 a 和 b 等所有行?如果重要的话,这在 Oracle 上.
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.
推荐答案
使用超前和滞后分析函数.
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,
这篇关于如何在此表中找到重复的连续值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文