Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1201260
  • 博文数量: 350
  • 博客积分: 10
  • 博客等级: 民兵
  • 技术积分: 5668
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-23 17:53
文章分类

全部博文(350)

文章存档

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

==================================

查看前例:

例1:按指定规则生成指定商品指定年限销售额


阅读(662) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~