T-SQL插入到除非

Question

我有下表：

Account              | Period   | Amount 
-------------------- | -------- | ------
Umbrella Corporation | 201601   | 100
Umbrella Corporation | 201602   | 50
Umbrella Corporation | 201608   | 100
Acme Inc             | 201504   | 85
Acme Inc             | 201504   | 90
Acme Inc             | 201512   | 40

句点是纯文本，但表示日期为YYYMM，有时可能也包括YYYMMDD，但为此目的，我忽略了这一天。

目标

为每个独特的帐户组合插入新行和每年12个可能的时间段(YYYYMM)。

示例

->如果表中已经存在唯一的组合，那么如果唯一的组合不存在，则不执行任何操作，请插入包含帐户、期间和金额的新行(新插入的行的金额总是为零)。 

期望结果

Account                 | Period    | Amount 
--------------------    | --------  | ------
Umbrella Corporation    | 201601    | 100
Umbrella Corporation    | 201602    | 100
Umbrella Corporation    | 201603    | 0
Umbrella Corporation    | 201604    | 0
Umbrella Corporation    | 201605    | 0
Umbrella Corporation    | 201606    | 0
Umbrella Corporation    | 201607    | 0
Umbrella Corporation    | 201608    | 100
Umbrella Corporation    | 201609    | 0
Umbrella Corporation    | 201610    | 0
Umbrella Corporation    | 201611    | 0
Umbrella Corporation    | 201612    | 0
Acme Inc                | 201501    | 0
Acme Inc                | 201502    | 0
Acme Inc                | 201503    | 0
Acme Inc                | 201504    | 85
Acme Inc                | 201504    | 90
Acme Inc                | 201605    | 0
Acme Inc                | 201506    | 0
Acme Inc                | 201507    | 0
Acme Inc                | 201508    | 0
Acme Inc                | 201509    | 0
Acme Inc                | 201510    | 0
Acme Inc                | 201511    | 0
Acme Inc                | 201512    | 40

我还没有找到一个坚实的起点来做这件事。我在这里发现了一些类似的问题，哪些使用INSERT、INTO....WHERE不存在或合并或连接。但理想情况下，如果可能的话，我希望在不需要另一张桌子的情况下实现这个结果。

如有任何指导，将不胜感激。我正在使用SQLServer2008R2。

*我试图提出一个好的问题，如果你认为可以更好，请告诉我。

Answer 1

一种选择是使用CTE创建一个计数表，以帮助确定可能的月份间隔。下面的示例演示了这种方法。

-- Create example table and sample data set.
CREATE TABLE Accounts
(
     Account    NVARCHAR(100)
    ,Period     NVARCHAR(10)
    ,Amount     FLOAT
)

INSERT INTO Accounts
(
 Account
,Period 
,Amount 
)
SELECT 'Umbrella Corporation' , '201601'   , 100    UNION ALL
SELECT 'Umbrella Corporation' , '201602'   , 50     UNION ALL
SELECT 'Umbrella Corporation' , '201608'   , 100    UNION ALL
SELECT 'Acme Inc'             , '201504'   , 85     UNION ALL
SELECT 'Acme Inc'             , '201504'   , 90     UNION ALL
SELECT 'Acme Inc'             , '201512'   , 40;

DECLARE @endPeriod NVARCHAR(10) = '201612';

WITH
E1(N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), 
E4(N) AS (SELECT 1 FROM E2 a, E2 b), 
Tally(N) AS     -- Create tally table. This tally table will return a maximum of a 1000 rows.
(
    SELECT  (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) FROM E4
)
, PossiblePeriods AS    --Determine possible monthly intervals for the year. Implementation assumes a maximum back fill of 5 years (60 months).
(
    SELECT   Account
            ,LEFT(CONVERT(NVARCHAR(10), DATEADD(MONTH, [T].N, [A].StartYear), 112), 6) AS Period
    FROM    (
                SELECT   Account
                        ,CONVERT(DATETIME, LEFT(MIN(Period), 4), 112) AS StartYear
                FROM    Accounts
                GROUP BY Account
            ) [A]
    CROSS JOIN 
            (
                SELECT TOP 60 N FROM Tally  -- Modify the TOP statement to control the number of months to back fill (up to a 1000 months)
            ) T
)
, NewPeriods AS -- Determine the new periods to add.
(
    SELECT   [P].Account
            ,[P].Period
            ,[A].Amount
    FROM    PossiblePeriods [P]
    LEFT OUTER JOIN Accounts [A]  ON [A].Account = P.Account AND A.Period = [P].Period
    WHERE   [P].Period <= @endPeriod
)
INSERT INTO Accounts
(
     Account
    ,Period 
    ,Amount 
)
SELECT  Account
        ,Period 
        ,0  
FROM    NewPeriods
WHERE   Amount IS NULL


--  Select out result.
SELECT      *
FROM        Accounts
ORDER BY    Account, Period

DROP TABLE Accounts

Answer 2

与使用其他表不同，您可以使用递归CTE来生成所需的所有静态值，并将现有数据连接到其中。

Answer 3

您可以在CROSS JOIN和accounts之间使用periods。

DECLARE @StartPeriod VARCHAR(8), @EndPeriod VARCHAR(8);
SET @StartPeriod = '201501';
SET @EndPeriod = '201612';

WITH Periods AS
(
    SELECT CONVERT(VARCHAR(6),DATEADD(MONTH,number,@StartPeriod + '01'),112) Period
    FROM master.dbo.spt_values
    WHERE type = 'P'
    AND CONVERT(VARCHAR(6),DATEADD(MONTH,number,@StartPeriod + '01'),112) <= @EndPeriod
)
SELECT  A.Account,
        B.Period,
        ISNULL(C.Amount,0) Amount
FROM (  SELECT DISTINCT Account
        FROM dbo.YourTable) A
CROSS JOIN Periods B
LEFT JOIN dbo.YourTable C
    ON A.Account = C.Account
    AND B.Period = C.Period;

此解决方案假定您还没有一个包含所需每个句点的表。如果您有一个，那么使用它，而不是在CTE中创建它们。