sql - 使用连接插入缺失的行

Question

我有一个返回这个派生表的 SQL 脚本。

MM/YYYY  Cat    Score
01/2012  Test1  17
02/2012  Test1  19
04/2012  Test1  15
05/2012  Test1  16
07/2012  Test1  14
08/2012  Test1  15
09/2012  Test1  15
12/2012  Test1  11
01/2013  Test2  10
02/2013  Test2  15
03/2013  Test2  13
05/2013  Test2  18
06/2013  Test2  14
08/2013  Test2  15
09/2013  Test2  14
12/2013  Test2  10

如您所见，我缺少一些 MM/YYYY(03/2012、06/2012、11/2012 等)。

我想用 Cat 和 0(零)填写缺少的 MM/YYYYs。

我试图加入一个表，其中包含将运行查询的范围的所有 MM/YYYY，但这只会返回第一次出现的缺失行，它不会为每只 Cat 重复(应该知道)。

所以我的问题是，我可以使用联接来执行此操作，还是必须在临时表中执行此操作，然后输出数据。

AHIGA，拉里R…

Answer 1

您需要交叉加入您的类别和范围内所有日期的列表。由于您没有发布任何表格结构，我将不得不稍微猜测一下您的结构，但假设您有一个日历表格，您可以使用如下内容:

SELECT  calendar.Date,
        Category.Cat,
        Score = ISNULL(Scores.Score, 0)
FROM    Calendar
        CROSS JOIN Catogory
        LEFT JOIN Scores
            ON Scores.Cat = Category.Cat
            AND Scores.Date = Calendar.Date
WHERE   Calendar.DayOfMonth = 1;

如果您没有日历表，您可以使用系统表Master..spt_values生成日期列表:

    SELECT  Date = DATEADD(MONTH, Number, '20120101')
    FROM    Master..spt_values
    WHERE   Type = 'P';

硬编码日期“20120101”是您范围内的第一个日期。

附录

如果您需要实际插入缺失的行，而不仅仅是填写空白的查询，您可以使用:

INSERT Scores (Date, Cat, Score)
SELECT  calendar.Date,
        Category.Cat,
        Score = 0
FROM    Calendar
        CROSS JOIN Catogory
WHERE   Calendar.DayOfMonth = 1
AND     NOT EXISTS
        (   SELECT  1
            FROM    Scores
            WHERE   Scores.Cat = Category.Cat
            AND     Scores.Date = Calendar.Date
        )

不过，在我看来，如果您有一个查询来填补空白，插入数据有点浪费时间。