SQL Server 2005 技术内幕 TSQL查询学习笔记chapter4之四输出已有范围和缺失范围

USE tempdb;

GO

IF OBJECT_ID('dbo.T1') IS NOT NULL

 DROP TABLE dbo.T1

GO

CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);

INSERT INTO dbo.T1(col1) VALUES(1);

INSERT INTO dbo.T1(col1) VALUES(2);

INSERT INTO dbo.T1(col1) VALUES(3);

INSERT INTO dbo.T1(col1) VALUES(100);

INSERT INTO dbo.T1(col1) VALUES(101);

INSERT INTO dbo.T1(col1) VALUES(103);

INSERT INTO dbo.T1(col1) VALUES(104);

INSERT INTO dbo.T1(col1) VALUES(105);

INSERT INTO dbo.T1(col1) VALUES(106);

任务一:缺失范围,输出如下表

思路:找到间断前的值,再加1,为每个间断的起点找到表中的下个值并减1

USE tempdb;

select A.col1+1 As start_range,

(select Min(col1) From T1 AS B Where B.col1>A.col1)-1 AS end_range

From T1 AS A

where not Exists(select * From T1 AS B where B.col1=A.col1+1 ) And A.col1<(select Max(col1) From T1)

任务二:缺失的值,输出如下表

 

思路:利用数字辅助表(连续的数字组成的表)

--创建数字辅助表

if Object_Id('Nums') is not Null

drop table Nums;

Go

Create Table Nums(

    n int not null Primary key

);

declare @max AS int,

@rc AS int;

set @max=200;

set @rc=1;--已插入到表达行数

insert into Nums (n) values(1);

--插入一半

While @rc*2<=@max

Begin

    insert into Nums Select n+@rc From Nums;

    Set @rc=@rc*2;

end

--插入另一半

insert into Nums Select n+@rc From Nums Where n+@rc<=@max;

执行下面的语句

use tempdb

select n AS DeletionNums From Nums

where n between (select min(col1) From T1) and (select max(col1) From T1)

and not Exists(select * From T1 where col1=n)

任务三:输出已有范围,输出如下表

 

思路:对连续范围进行分组,然后找出此分组中的最小,最大值

首先为每一行数据附加行

use tempdb

select col1,Row_Number() Over(Order By col1) AS rowIndex From T1

 

观察col1的值递增的同时和行号的关系.发现连续col1中 col1-rowIndex是没有变化的,比如100-4=96;101-5=96等等.所以可以得到对连续范围的分组,输出每组的最大最小值.

最后的sql语句如下:

use tempdb

select min(col1) AS start_range,max(col1) AS end_range From

(select col1,col1-Row_Number() Over(Order By col1) AS grp From T1) AS D

Group By grp