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种不同的方法，并且作者给予了自己的对于方法“好与坏”的评判标准。

那么作者想要表达的本质是什么呢？

思考一个问题的时候，我们不如从一个最表面的现象开始逐步往里分析，首先，需要看的顺眼，这样有助于下面的分析问题。

 

Technorati 标签: sql2005,t-sql,query