2013年（350）
分类： Mysql/postgreSQL
2013-04-25 10:34:08
查询字段a的值连续三条以上相同的记录
初始表数据如下：
a b c
- - -
1 2 3
1 4 5
1 3 6
2 3 3
1 5 7
2 5 8
1 6 9
1 2 3
1 4 5
1 3 6
要求用实现如下效果：
a b c
- - -
1 2 3
1 4 5
1 3 6
1 6 9
1 2 3
1 4 5
1 3 6
建表语句如下：
create table tmp2 (a number,b number, c number);
insert into tmp2 values (1,2,3);
insert into tmp2 values (1,4,5);
insert into tmp2 values (1,3,6);
insert into tmp2 values (2,3,3);
insert into tmp2 values (1,5,7);
insert into tmp2 values (2,5,8);
insert into tmp2 values (1,6,9);
insert into tmp2 values (1,2,3);
insert into tmp2 values (1,4,5);
insert into tmp2 values (1,3,6);
commit;
解题思路：
这道题看起来非常简单，我们甚至一眼就能看出来哪些记录是连接3条相同的，但千万不要被其简单的表象迷惑了，特别是那些下意识就能得出结论的问题，这往往会让我们的思维陷入到自我的思维误区中，而不再以计算机的执行模式去理解问题，因此这题核心要解决的问题就是将我们的思维方式转换成sql可以理解的记数方式。
先来理一理我们的逻辑，看看能否转换成对应的SQL操作：
首先肯定是拿上一条与下一条做对比，看看是否相同--->lead,lag分析函数可以实现这一点
计算相同数--->count分析函数可以实现，但是这里面有一个问题，分析函数虽然是逐条对比生成结果，但此处我们的依据是是否相同的字段值，假设该字段值为0或1的话，count() over(partition by )就没有了依照，因此我们需要先将比较的结果字段通过sum() over(order by rownum)计算相加，以便生成分区用的字段。
如果计数>3则这些记录符合我们的需求
OK，思路理清了，下面一步步来试试，首先生成比较是否相同的字段：
JSSWEB> select a.*,
2 rownum rn,
3 decode(a, lag(a, 1, a) over(order by rownum), 0, 1) na
4 from tmp2 a
5 ;
A B C RN NA
---------- ---------- ---------- ---------- ----------
1 2 3 1 0
1 4 5 2 0
1 3 6 3 0
2 3 3 4 1
1 5 7 5 1
2 5 8 6 1
1 6 9 7 1
1 2 3 8 0
1 4 5 9 0
1 3 6 10 0
10 rows selected
*rownum列是为了排序用
然后生成用于partition的列
JSSWEB> select b.*, sum(na) over(order by rn) so
2 from (select a.*,
3 rownum rn,
4 decode(a, lag(a, 1, a) over(order by rownum), 0, 1) na
5 from tmp2 a) b
6 ;
A B C RN NA SO
---------- ---------- ---------- ---------- ---------- ----------
1 2 3 1 0 0
1 4 5 2 0 0
1 3 6 3 0 0
2 3 3 4 1 1
1 5 7 5 1 2
2 5 8 6 1 3
1 6 9 7 1 4
1 2 3 8 0 4
1 4 5 9 0 4
1 3 6 10 0 4
10 rows selected
这下就清晰多了，剩下的就没难度了，count() over()生成数量，取数量大于2的记录即可：
JSSWEB> select a,b,c from(
2 select c.*, count(so) over(partition by so) ct
3 from (select b.*, sum(na) over(order by rn) so
4 from (select a.*,
5 rownum rn,
6 decode(a, lag(a, 1, a) over(order by rownum), 0, 1) na
7 from tmp2 a) b) c
8 )where ct>=3
9 ;
A B C
---------- ---------- ----------
1 2 3
1 4 5
1 3 6
1 6 9
1 2 3
1 4 5
1 3 6
7 rows selected
==================================
查看前例：
