学步园

[转自]http://hi.baidu.com/wonght/blog/item/071bb9cbc9f40a05bf09e6c4.html

测试表与测试数据

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