SqlServer 2005 T-SQL Query 学习笔记(5)
作者提供了一种计算出数据存在的范围和缺少的范围的方法,他提醒我们要注意这些技术本身,而不仅仅是数据。
我们有一个数据列如下:1,2,3,100,101,103,104,105,106
返回结果1(丢失的范围):
start_range end_range
4----------99
102-------102
返回结果2(存在的范围):
start_range end_range
1----------3
100-------101
103-------106
丢失的范围(GAPS)
方法一:
(步骤)
1.找到每个间断的点,然后把这些点加1。
2.对于每个丢失范围开始的点,在现有表里找到下一个存在的值,并减1。
有了这些理论的基础,现在就可以编码,如下所示:
返回每个间断的点(步骤1):
SELECT col1 FROM dbo.T1 AS A WHERE NOT EXISTS (SELECT * FROM dbo.T1 AS B WHERE B.col1 = A.col1 + 1);
间断的点加1(步骤1):
SELECT col1 + 1 AS start_range FROM dbo.T1 AS A WHERE NOT EXISTS (SELECT * FROM dbo.T1 AS B WHERE B.col1 = A.col1 + 1) AND col1 < (SELECT MAX(col1) FROM dbo.T1);
在现有表找到断点的下一个值,并减1(步骤2):
SELECT col1 + 1 AS start_range, (SELECT MIN(col1) FROM dbo.T1 AS B WHERE B.col1 > A.col1) - 1 AS end_range FROM dbo.T1 AS A WHERE NOT EXISTS (SELECT * FROM dbo.T1 AS B WHERE B.col1 = A.col1 + 1) AND col1 < (SELECT MAX(col1) FROM dbo.T1);
方法二:(简单,而且更容易理解)
(步骤)
1.对于每一个存在的值,匹配下一个存在的值,生成了相邻的对。
2.保证每一个对,都是右-左>1的关系。
3.对于保留的对,在现有的加上1,紧接的下一个减1。
编码:
生成所有相邻对(步骤1):
SELECT col1 AS cur, (SELECT MIN(col1) FROM dbo.T1 AS B WHERE B.col1 > A.col1) AS nxt FROM dbo.T1 AS A;
筛选相邻对,并进行加1减1操作(步骤2,3):
SELECT cur + 1 AS start_range, nxt - 1 AS end_range FROM (SELECT col1 AS cur, (SELECT MIN(col1) FROM dbo.T1 AS B WHERE B.col1 > A.col1) AS nxt FROM dbo.T1 AS A) AS D WHERE nxt - cur > 1;
对于这2种方法,经过作者的实验,性能都差不多,但是作者推荐是第2种,因为它简单而且便于理解,易于维护。
存在的范围 (Islands)
方法一:
解决思想:把符合某些条件的数据通过某一分组因数,归到一组。只要这分组因数成立,那么我们取得各个组里的最大,最小值,即得到了范围。
步骤1(按分组因数进行编码):
SELECT col1, (SELECT MIN(col1) FROM dbo.T1 AS B WHERE B.col1 >= A.col1 --找到临界点,即这个分组因数 AND NOT EXISTS (SELECT * FROM dbo.T1 AS C WHERE B.col1 = C.col1 - 1)) AS grp FROM dbo.T1 AS A;
步骤2(按分组因数进行分组,取最大,最小值):
SELECT MIN(col1) AS start_range, MAX(col1) AS end_range FROM (SELECT col1, (SELECT MIN(col1) FROM dbo.T1 AS B WHERE B.col1 >= A.col1 AND NOT EXISTS (SELECT * FROM dbo.T1 AS C WHERE B.col1 = C.col1 - 1)) AS grp FROM dbo.T1 AS A) AS D GROUP BY grp;
方法二:
作者认为此方法更简单,直观,更好的效率。
步骤1(给每个数字进行INDEX的编码,标示序号):
SELECT col1, ROW_NUMBER() OVER(ORDER BY col1) AS rn FROM dbo.T1;
步骤2(用标号减去实际的COL1的数字,相当于自动进行了分组):
SELECT col1, col1 - ROW_NUMBER() OVER(ORDER BY col1) AS diff FROM dbo.T1;
步骤3(分组,取最大最小值):
SELECT MIN(col1) AS start_range, MAX(col1) AS end_range FROM (SELECT col1, col1 - ROW_NUMBER() OVER(ORDER BY col1) AS grp FROM dbo.T1) AS D GROUP BY grp;
这篇文章的本质:
这篇文章展示了2个实际的例子,每个例子使用2种不同的方法,并且作者给予了自己的对于方法“好与坏”的评判标准。
那么作者想要表达的本质是什么呢?
思考一个问题的时候,我们不如从一个最表面的现象开始逐步往里分析,首先,需要看的顺眼,这样有助于下面的分析问题。