使用连接插入缺失的行 [英] Inserting missing rows with a join

问题描述

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

I have a SQL script that returns this derived table.

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 等).

As you can see, I am missing some MM/YYYYs (03/2012, 06/2012, 11/2012, etc).

我想用 Cat & 填写缺少的 MM/YYYYs0(零)形成分数.

I would like to fill in the missing MM/YYYYs with the Cat & a 0 (zero) form the score.

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

I have tried to join a table that contains the all MM/YYYY for the ranges the query will be run, but this only returns the missing rows for the first occurrence, it does not repeat for each Cat (should have known that).

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

So my question is this, can I do this using a join or will I have to do this in a temp table, and then output the data.

阿希加，拉里……

推荐答案

试试这个 -

DECLARE @temp TABLE (FDOM DATETIME, Cat NVARCHAR(50), Score INT)

INSERT INTO @temp (FDOM, Cat, Score)
VALUES 
    ('20120101', 'Test1', 17),('20120201', 'Test1', 19),
    ('20120401', 'Test1', 15),('20120501', 'Test1', 16),
    ('20120701', 'Test1', 14),('20120801', 'Test1', 15),
    ('20120901', 'Test1', 15),('20121001', 'Test1', 13),
    ('20121201', 'Test1', 11),('20130101', 'Test1', 10),
    ('20130201', 'Test1', 15),('20130301', 'Test1', 13),
    ('20130501', 'Test1', 18),('20130601', 'Test1', 14),
    ('20130801', 'Test1', 15),('20130901', 'Test1', 14),
    ('20131201', 'Test1', 10),('20120601', 'Test2', 10)

;WITH enum AS 
(
    SELECT Cat, StartDate = MIN(FDOM), EndDate = MAX(FDOM)
    FROM @temp
    GROUP BY Cat

    UNION ALL

    SELECT Cat, DATEADD(MONTH, 1, StartDate), EndDate
    FROM enum 
    WHERE StartDate < EndDate
)
SELECT e.StartDate, t.Cat, Score = ISNULL(t.Score, 0)
FROM enum e
LEFT JOIN @temp t ON e.StartDate = t.FDOM AND e.Cat = t.Cat
ORDER BY e.StartDate, t.Cat

这篇关于使用连接插入缺失的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！