统计数列中是连续数的个数

数据库环境：SQL SERVER2008R2

今天在群里看到一位网友提的需求，要求统计一组数据中前3位是连续数的个数，具体看贴图。

实现这个需求蛮简单的，说下我的思路：

取前3位，然后减去123，再和111求余，如果结果是0，说明这3位数连续。

是不是很简单呀，下面直接上代码。

/*数据准备*/
WITH    x0
          AS ( SELECT   1234 AS tn
               UNION ALL
               SELECT   1235 AS tn
               UNION ALL
               SELECT   1236 AS tn
               UNION ALL
               SELECT   2345 AS tn
               UNION ALL
               SELECT   2346 AS tn
               UNION ALL
               SELECT   3456 AS tn
               UNION ALL
               SELECT   2456 AS tn
             )
    /*计算、输出*/
    SELECT  tn1 ,
            COUNT(*) AS cn
    FROM    ( SELECT    tn ,
                        tn / 10 AS tn1 ,
                        tn / 10 - 123 AS tn2
              FROM      x0
            ) t
    WHERE   tn2 % 111 = 0
    GROUP BY tn1
    ORDER BY tn1

只需变一下思路，问题就解决了。我贴一下结果。

（本文完）

 

转载于:https://www.cnblogs.com/boss-he/p/4531716.html