锐利的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                                                                      存货情况表

相对于大型数据库而言，第一种架构设计对于查询语句编写方面会更方便一些。如果数据需要驻留在一些手持设备上，多数开发人员会更喜欢第二种架构设计，因为它能够节省宝贵的存储空间。尤其是当表的数据量非常大时，这种设计方式更能显示出它的优势。这里我们将以第二种架构设计方式来演示查询的创建方法，下面是创建示例表的语句：

CREATE TABLE Freights(Numb int NOT NULL);

INSERT INTO Freights VALUES

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

1、查找剩余空位区间和剩余空位编号

要查找剩余空位区间，就是要找出表2所示的数值范围。

表2                                                                    剩余空位区间

要找出这些区间的开始和结束编号，需要在间断之前的值加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                                                          每一货位号的下一货位号

可以看出，只要找出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                                                        比当前货位号小的空货位号

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                                                                     数据分组

通过上表可以很清晰地看出数据分组情况，因此，我们给出下面的最终查询语句，查询结果如表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                                                                  已用货位区间

2楼zhanghongju昨天 13:30

不是大师，探讨吧。n20应当是ORDER BY这个子句造成的吧，因为CreatedDateTime创建有索引。n考虑考虑对KeyWord列创建SQL Server的包含列索引方式，看看能否提高性能：n当查询中的所有列都作为键列或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。因为查询优化器可以在索引中找到所有列值，不需要访问表或聚集索引数据，从而减少磁盘I/O操作。当索引包含查询引用的所有列时，称为“覆盖查询”。n例如，假设要设计覆盖下列查询的索引。nUSE AdventureWorks;nGOnSELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCodenFROM Person.AddressnWHERE PostalCode BETWEEN N'98000' and N'99999';n如果要使用覆盖查询，必须在索引中定义每列。尽管可以将所有列定义为键列，但这样键的合计大小为334字节。由于实际上用作搜索条件的列只有PostalCode列（长度为30字节），所以更好的索引设计应该将PostalCode定义为键列，其他所有列作为非键列。参考下面的语句：nUSE AdventureWorks;nGOnCREATE INDEX IX_Address_PostalCode nON Person.Address (PostalCode) nINCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

1楼manwea昨天 11:55

大师,问您一个问题:nSELECT DISTINCT VWK.`VideoId` FROM `FV_VideoWithKeyword` VWK nWHERE nVWK.`ChannelId` IN (3, 6) ANDnVWK.`KeyWord` IN ('酷6拍客') ORDER BY VWK.`CreatedDateTime` DESC LIMIT 0,20n这个数据库里有两个索引a(ChannelId, KeyWord), b(CreatedDateTime),最后用的居然是b, 而且扫描的行数为20, 如果删去这两个索引新建一个c(ChannelId, KeyWord, CreatedDateTime)索引,那会扫描全部行数,但如果加上b索引,那就会使用b索引,扫描行数又会成为20, 这,,,是为什么?