microsoft SQL中的笛卡尔乘积的应用（查询缺少）

转自百度http://hi.baidu.com/wangzhiqing999/blog/item/8db9453dafb73bfc3b87ce51.html

ps1：特别喜欢这种blog的写法。有案例，有分析过程，有语句，有结果。

ps2：有时间尝试一下oracle下面的这个sql写法，应该没有这样的复杂。

SQL查询案例：寻找连续日期中残缺的数据

2010-10-10 21:08

测试表与测试数据

CREATE TABLE TestDataCheck (

id    varchar(5),     -- 设备ID

dates datetime                                -- 日期

)

 

INSERT INTO TestDataCheck VALUES ('设备1', '2010-10-01 00:00:00');

INSERT INTO TestDataCheck VALUES ('设备1', '2010-10-02 00:00:00');

INSERT INTO TestDataCheck VALUES ('设备1', '2010-10-03 00:00:00');

INSERT INTO TestDataCheck VALUES ('设备1', '2010-10-04 00:00:00');

INSERT INTO TestDataCheck VALUES ('设备1', '2010-10-06 00:00:00');

INSERT INTO TestDataCheck VALUES ('设备1', '2010-10-07 00:00:00');

INSERT INTO TestDataCheck VALUES ('设备1', '2010-10-08 00:00:00');

INSERT INTO TestDataCheck VALUES ('设备1', '2010-10-09 00:00:00');

 

 

INSERT INTO TestDataCheck VALUES ('设备2', '2010-10-01 00:00:00');

INSERT INTO TestDataCheck VALUES ('设备2', '2010-10-02 00:00:00');

INSERT INTO TestDataCheck VALUES ('设备2', '2010-10-03 00:00:00');

INSERT INTO TestDataCheck VALUES ('设备2', '2010-10-07 00:00:00');

INSERT INTO TestDataCheck VALUES ('设备2', '2010-10-08 00:00:00');

INSERT INTO TestDataCheck VALUES ('设备2', '2010-10-09 00:00:00');

INSERT INTO TestDataCheck VALUES ('设备2', '2010-10-10 00:00:00');

 

要求

取得 所有设备， 在2010-10-01 至 2010-10-10之间，缺少的记录

 

思路

首先 需要一个 10 天的日期序列

然后，需要一个 10天 与 设备的 排列组合

最后，用排列组合 与 目标表关联检索 缺少的数据。

 

实现

第一步，创建日期序列

暂时用 1-10 来存储 日期的变化。最后与主表关联的时候，再作日期的处理。

SELECT TOP 10

ROW_NUMBER() OVER (ORDER BY (SELECT 0) ) AS NO

FROM sys.tables

 

NO

--------------------

                   1

                   2

                   3

                   4

                   5

                   6

                   7

                   8

                   9

                  10

 

第二步， 日期、设备 排列组合

SELECT

all_id.id,

all_day_no.no

FROM

(

SELECT TOP 10

    ROW_NUMBER() OVER (ORDER BY (SELECT 0) ) AS NO

FROM sys.tables

) AS all_day_no,

(

SELECT DISTINCT

    id

FROM

    TestDataCheck

) AS all_id

 

id    no

----- --------------------

设备1                      1

设备2                      1

设备1                      2

设备2                      2

设备1                      3

设备2                      3

设备1                      4

设备2                      4

设备1                      5

设备2                      5

设备1                      6

设备2                      6

设备1                      7

设备2                      7

设备1                      8

设备2                      8

设备1                      9

设备2                      9

设备1                     10

设备2                     10

 

第三步 排列组合与目标表关联

 

 

SELECT

CONVERT(DATETIME, '2010-09-30 00:00:00') + AllTestDataCheck.NO AS 日期,

AllTestDataCheck.id

FROM

(

SELECT

    all_id.id,

    all_day_no.no

FROM

    (

    SELECT TOP 10

      ROW_NUMBER() OVER (ORDER BY (SELECT 0) ) AS NO

    FROM sys.tables

    ) AS all_day_no,

    (

    SELECT DISTINCT

      id

    FROM

      TestDataCheck

    ) AS all_id

) AllTestDataCheck

LEFT JOIN TestDataCheck

    ON (AllTestDataCheck.id = TestDataCheck.id

        AND CONVERT(DATETIME, '2010-09-30 00:00:00') + AllTestDataCheck.NO = TestDataCheck.dates)

WHERE

TestDataCheck.dates IS NULL

 

 

 

执行结果

日期                      id

----------------------- -----

2010-10-04 00:00:00.000 设备2

2010-10-05 00:00:00.000 设备1

2010-10-05 00:00:00.000 设备2

2010-10-06 00:00:00.000 设备2

2010-10-10 00:00:00.000 设备1