鋒利的SQL：數字範圍統計

這是在做一個大型貨場租賃系統時遇到的問題，在計算貨場剩餘存儲空間時，不僅僅需要知道哪些貨位是空閑的，還要能夠判斷出哪些貨位之間是連續的。因為在新貨物入場時，可以判斷這些貨物是否可以堆放在一起，而不是放在不連續的多個貨位上，這樣更便于管理，并且在出貨時也更加迅速。

假設這個貨場共有100個貨位，現在已存放貨物的貨位是1、2、3、4、87、89、99、100，則剩餘空位是5～86、88、90～98。資料庫的設計方式一般有兩種：一種是在表中為每個貨位建一條記錄，類似表1所示的結構設計；另一種設計方式是僅将存放有貨物的貨位号放在表中，也就是存貨情況表中僅有貨位編号列，存放1、2、3、4、87、89、99、100這幾個數值。

表1                                                                      存貨情況表

貨位編号	是否存放有貨物（1-是，0-否）
1	1
2	1
3
...	...

相對于大型資料庫而言，第一種架構設計對于查詢語句編寫方面會更友善一些。如果資料需要駐留在一些手持裝置上，多數開發人員會更喜歡第二種架構設計，因為它能夠節省寶貴的存儲空間。尤其是當表的資料量非常大時，這種設計方式更能顯示出它的優勢。這裡我們将以第二種架構設計方式來示範查詢的建立方法，下面是建立示例表的語句：

CREATE TABLE Freights(Numb int NOT NULL);

INSERT INTO Freights VALUES

 (1),(2),(3),(4),(87),(89),(99),(100);

1、查找剩餘空位區間和剩餘空位編号

要查找剩餘空位區間，就是要找出表2所示的數值範圍。

表2                                                                    剩餘空位區間

貨位開始編号	貨位結束編号
5	86
88	88
90	98

要找出這些區間的開始和結束編号，需要在間斷之前的值加1，在下一組編号開始之間的值減1。例如，表中的5～86是在4的基礎上加1、在87的基礎上減1得來的。

首先來看一下下面的語句，用于擷取每個貨位号的下一貨位号，得到的結果如表3所示。

SELECT F1.Numb AS n1,

      (SELECT MIN(F2.Numb)

       FROMFreights AS F2

       WHERE F2.Numb > F1.Numb) AS n2

FROM Freights AS F1;

表3                                                          每一貨位号的下一貨位号

n1	n2
1	2
2	3
3	4
4	87
87	89
89	99
99	100
100	NULL

可以看出，隻要找出n2-n1大于1的貨位組，并在n1上加1，在n2上減1，就可以得到表19-7所示的剩餘空位區間。參考下面的語句：

SELECT n1 + 1 AS start_id, n2 - 1 AS end_id

FROM (SELECT F1.Numb AS n1,

           (SELECT MIN(F2.Numb)

            FROM Freights AS F2

            WHERE F2.Numb > F1.Numb) AS n2

     FROMFreights AS F1) AS F3

WHERE n2 - n1 >1;

上面是使用子查詢的方式作為中間結果的存儲，也可以使用CTE方式，參考下面的語句：

WITH F3 (n1, n2)

AS

(

  SELECTF1.Numb AS n1,

         (SELECT MIN(F2.Numb)

          FROM Freights AS F2

          WHERE F2.Numb > F1.Numb) AS n2

  FROMFreights AS F1

)

SELECT n1 + 1 AS start_id, n2 -1 AS end_id

FROM F3

WHERE n2 - n1 >1;

而下面的語句則是使用内聯接和分組計算的方法計算剩餘空位區間，與上面的兩種方式相比，此方式在GROUP BY時多出了排序操作，查詢開銷較大。

SELECT (F1.Numb + 1) AS start,

      (MIN(F2.Numb - 1)) AS finish

FROM Freights AS F1

 INNER JOINFreights AS F2

   ONF2.Numb > F1.Numb      

GROUP BY F1.Numb

HAVING (F1.Numb + 1) < MIN(F2.Numb);

如果希望傳回的不是剩餘空位區間，而是剩餘空位編号，則需要建立一個全部的貨位編号表。下面的語句使用了遞歸CTE循環來建立1～100的貨位編号。

WITH Numbs AS

(

  SELECT 1AS n

  UNION ALL

  SELECT n+ 1 FROM Numbs WHERE n < 100

)

SELECT n FROM Numbs OPTION(MAXRECURSION 0);

隻要全部貨位編号在Freights表中不存在，則表示該貨位号沒有使用，參考下面的語句：

WITH Numbs AS

(

  SELECT 1AS n

  UNION ALL

  SELECT n+ 1 FROM Numbs WHERE n < 100

)

SELECT n FROM Numbs

WHERE n NOT IN (SELECT Numb FROM Freights)

OPTION(MAXRECURSION 0);

如果不需要傳回全部的空貨位号，而是幾個的話，可以使用下面的查詢語句。它使用了從SQL Server 2005開始支援的視窗函數進行編号，傳回比目前編号小的空貨位号。查詢結果如表4所示。對于其中的重複數值，可以使用DISTINCT關鍵字進行過濾。

SELECT Numb, rn, (Numb - rn) AS available_Numb

FROM (SELECT Numb, ROW_NUMBER() OVER (ORDER BYNumb)

     FROMFreights) AS F(Numb, rn)

WHERE rn <> Numb     

表4                                                        比目前貨位号小的空貨位号

Numb	rn	available_Numb
87	5	82
89	6	83
99	7	92
100	8	92

2、查找已用貨位區間

現在已經使用的貨位是1、2、3、4、87、89、99、100，已用貨位區間即：1～4、87～87、89～89、99～100。這些區間實際上是一組連續編号中的最小值和最大值，如1～4是貨位1、2、3、4中的最小值和最大值。現在關鍵的問題是如何判斷出這是一組數值，通過上面的表2你也許會發現一個有趣的問題，99、100通過Numb – rn後得到的數值是相同的，說明這是一組數值。下面是去掉WHERE子句後的查詢語句，結果如表5所示。

SELECT Numb, rn, (Numb - rn) AS available_Numb

FROM (SELECT Numb, ROW_NUMBER() OVER (ORDER BYNumb)

     FROMFreights) AS F(Numb, rn)

表5                                                                     資料分組

Numb	rn	available_Numb
1	1
2	2
3	3
4	4
87	5	82
89	6	83
99	7	92
100	8	92

通過上表可以很清晰地看出資料分組情況，是以，我們給出下面的最終查詢語句，查詢結果如表6所示。

SELECT MIN(Numb) AS start, MAX(Numb) AS finish

FROM (

 SELECTNumb, rn, (Numb - rn) AS available_Numb

 FROM(SELECT Numb, ROW_NUMBER() OVER (ORDER BY Numb)

       FROMFreights) AS F(Numb, rn)

) AS G(Numb, rn, available_Numb)

GROUP BY available_Numb;

表6                                                                  已用貨位區間

start	finish
1	4
87	87
89	89
99	100