查询连续的数字

2013-01-21 11:44:37 283 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

编写一个 SQL 查询，查找所有至少连续出现三次的数字题目如下：编写一个 SQL 查询，查找所有至少连续出现三次的数字。 +----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ 例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。 +-----------------+ | ConsecutiveNums | +- 阅读详情

USE tempdb 

IF OBJECT_ID('test') IS NOT NULL

  DROP TABLE test;      

GO

CREATE TABLE test       

( 

  id int

);

GO

INSERT INTO test VALUES 

(1),(2),(3),(5),(8),(9),(10),(11);

GO

-------------------------------------



-------------------------------------

/*

id_star id_end

------- -------

1       3

8       11

*/

...全文

sqlserver- 查询至少3个连续数字练习：连续出现的数字（四种方法- 3表链接；数值规律；表函数；存储过程；）；拓展：1.对比EXEC & sp_executesql; 2.表变量用法；3.存储过程；4.认识表函数（& 标量函数） & 案例；阅读详情

amazeyeli

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

amazeyeli 2013-01-21

打赏
举报

嗯,我懂了,去除掉连续的较小的所有笛卡儿积结帖

amazeyeli 2013-01-21

打赏
举报

引用 2 楼 OrchidCat 的回复:

3楼的比较好理解,2楼的理解有点问题

 SELECT    id ,
                    ( SELECT    MIN(id)
                      FROM      Test AS b
                      WHERE     b.id >= a.id
                                AND NOT EXISTS ( SELECT 1
                                                 FROM   test AS c
                                                 WHERE  b.id = c.id - 1 )
                    ) AS grp
          FROM      test AS a
--改成这样,运行结果一样,a,b构成的笛卡儿积除掉什么来着
SELECT a.id,MIN(b.id)AS grp
FROM test AS a
JOIN test AS b
ON b.id >= a.id
WHERE NOT EXISTS
(SELECT 1 FROM test AS c WHERE b.id=c.id-1)  
GROUP BY a.id

开着拖拉机泡妞 2013-01-21

打赏
举报

--确实范围和现有范围（也称间断和孤岛问题）
--1、缺失范围（间断）
/*
收集人：TravyLee
时间：2012-03-25
如有引用，请标明“此内容源自MSSQL2008技术内幕之T-SQL”
*/
/*
求解间断问题有几种方法，小弟我选择性能较高的三种（使用游标的方法省略
有兴趣不全的大哥大姐请回复）
---------------------------------------------------------------------
间断问题的解决方案1；使用子查询
step 1：找到间断之前的值，每个值增加一个间隔
step 2：对于没一个间断的起点，找出序列中现有得值，再减去一个间隔
本人理解为：找到原数据表中的值加一减一是否存在，若有不妥，望纠正
生成测试数据:
go
if object_id('tbl')is not null 
drop table tbl
go
create table tbl(
id int not null
)
go
insert tbl
values(2),(3),(11),(12),(13),(27),(33),(34),(35),(42)
要求：找到上表数据中的不存在的id的范围，
--实现输出结果：
/*
开始范围 结束范围
 4        10
 14       26
 28       32
 36       41
 */
 按照每个步骤实现：
 step 1：找到间断之前的值，每个值增加一个间隔
 我们可以清楚的发现，要找的间断范围的起始值实际上就是我们
 现有数据中的某些值加1后存不存在现有数据表中的问题，通过
 子查询实现：
 
 select id+1 as start_range from tbl as a
 where not exists(select 1 from tbl as b
 where b.id=a.id+1)and id<(select max(id) from tbl)
 --此查询语句实现以下输出：
 /*
 start_range
 4
 14
 28
 36
 */
 step 2：对于没一个间断的起点，找出序列中现有得值，再减去一个间隔
 
 select id+1 as start_range,(select min(b.id) from tbl as b
 where b.id>a.id)-1 as end_range
 from tbl a where not exists(select 1 from tbl as b
                        where b.id=a.id+1)
     and id<(select max(id) from tbl)
 --输出结果：
 /*
   start_range	end_range
   4	10
   14	26
   28	32
   36	41
 */
通过以上的相关子查询我们实现了找到原数据表中的间断范围。
而且这种方式的效率较其他方式有绝对的优势


间断问题的解决方案2；使用子查询（主意观察同1的区别）
step 1:对每个现有的值匹配下一个现有的值，生成一对一对的当
       前值和下一个值
step 2:只保留下一个值减当前值大于1的间隔值对
step 3:对剩下的值对，将每个当前值增加1个间隔，将每个下一
       个值减去一个间隔

--转换成T-SQL语句实现：
--step 1:
select id as cur,(select min(b.id) from tbl b where
         b.id>a.id) as nxt from tbl a
--此子查询生成的结果：
/*
 cur	nxt
 2	 3
 3	 11
 11	 12
 12	 13
 13	 27
 27	 33
 33	 34
 34	 35
 35	 42
 42	 NULL
 */
 step 2 and step 3:
 select cur+1 as start_range,nxt-1 as end_range
 from (select id as cur,(select min(b.id) from tbl b 
 where b.id>a.id) as nxt from tbl a ) as d
      where nxt-cur>1
--生成结果：
/*
 start_range	 end_range
 4	 10
 14	 26
 28	 32
 36	 41
*/
 间断问题的解决方案3；使用排名函数实现
 
 此种方法与第二种类似,这里我一步实现：
 
 ;with c as
 (
   select id,row_number()over(order by id) as rownum
   from tbl
 )
 select cur.id+1 as strat_range,nxt.id-1 as end_range
        from c as cur join c as nxt
   on nxt.rownum=cur.rownum+1
  where nxt.id-cur.id>1

--输出结果：
 /*
 strat_range	end_range
 4	 10
 14	 26
 28	 32
 36	 41
 */
 
*/
--2、先有范围（孤岛）
/*
以上测试数据，试下如下输出：
/*
开始范围 结束范围
2        3
11       13
27       27
33       35
42       42
*/
和间断问题一样，孤岛问题也有集中解决方案，这里也只介绍三种
省略了用游标的实现方案：

孤岛问题解决方案1：使用子查询和排名计算
step 1:找出间断之后的点，为他们分配行号（这是孤岛的起点）
step 2:找出间断之前的点，为他们分配行号（这是孤岛的终点）
step 3:以行号相等作为条件，匹配孤岛的起点和终点

--实现代码:
    with startpoints as
    (
      select id,row_number()over(order by id) as rownum
           from tbl as a where not exists(
        select 1 from tbl as b where b.id=a.id-1) 
    ),
    endpoinds as
    (
      select id,row_number()over(order by id) as rownum
          from tbl as a where not exists(
        select 1 from tbl as b where b.id=a.id+1)
    )
    select s.id as start_range,e.id as end_range
    from startpoints as s
    inner join endpoinds as e
    on e.rownum=s.rownum
--运行结果:   
/*
 start_range	end_range
 2	3
 11	13
 27	27
 33	35
 42	42
*/

孤岛问题解决方案2：使用基于子查询的组标识符

--直接给出代码：

with d as
(
  select id,(select min(b.id) from tbl b where b.id>=a.id
      and not exists (select * from tbl c where c.id=b.id+1)) as grp
  from tbl a
)
select min(id) as start_range,max(id) as end_range
from d group by grp
/*
start_range	end_range
2	3
11	13
27	27
33	35
42	42
*/


孤岛问题解决方案3：使用基于子查询的组标识符:

step 1:按照id顺序计算行号:
   select id ,row_number()over(order by id) as rownum from tbl
/*
id	rownum
2	1
3	2
11	3
12	4
13	5
27	6
33	7
34	8
35	9
42	10
*/
step 2：生成id和行号的差:
   select id,id-row_number()over(order by id) as diff from tbl
/*
id	diff
2	1
3	1
11	8
12	8
13	8
27	21
33	26
34	26
35	26
42	32
*/
这里解释一下这样做的原因；
   因为在孤岛范围内，这两个序列都以相同的时间间隔来保持增长，所以
   这时他们的差值保持不变。只要遇到一个新的孤岛，他们之间的差值就
   会增加。这样做的目的为何，第三步将为你说明。
step 3:分别取出第二个查询中生成的相同的diff的值的最大id和最小id
    with t as(
      select id,id-row_number()over(order by id) as diff from tbl
    )
    select min(id) as start_range,max(id) as end_range from t
       group by diff
/*
start_range	end_range
2	3
11	13
27	27
33	35
42	42
*/

求孤岛问题，第三种方法效率较前两种较高，具有比较强的技巧性
希望在实际运用中采纳。
*/

Mr_Nice 2013-01-21

打赏
举报

--2005 往上版本可用这个

SELECT MIN(id) AS start_range,MAX(id) AS end_range
FROM (SELECT id,id -ROW_NUMBER() OVER(ORDER BY id) AS grp FROM test ) AS d
GROUP BY grp
HAVING MIN(id)<>MAX(id)

Mr_Nice 2013-01-21

打赏
举报

SELECT  MIN(id) AS start_range ,
        MAX(id) AS end_range
FROM    ( SELECT    id ,
                    ( SELECT    MIN(id)
                      FROM      Test AS b
                      WHERE     b.id >= a.id
                                AND NOT EXISTS ( SELECT 1
                                                 FROM   test AS c
                                                 WHERE  b.id = c.id - 1 )
                    ) AS grp
          FROM      test AS a
        ) AS d
GROUP BY grp
HAVING MIN(id)<>MAX(id)

/*
start_range	end_range
1			3
8			11*/

a578452523 2013-01-21

打赏
举报

神马意思？麻烦详细点？

查询连续的数字

相关推荐