SQL-不存在时添加行

Question

假设我有一个返回结果的查询：

Project Year Type Amt
PJ00001 2012 1    1000
PJ00001 2012 2    1000
PJ00001 2011 1    1000
PJ00002 2012 1    1000

我想要的：每个项目每年都有2行类型。如果该行不存在，请将其添加到AMT = 0的结果中。
例如：
-PJ00001在2012年有2行1,2行 ->好吧。但是在2011年，它只有1行1->我们添加一行：PJ00001 2011 2 0
-PJ00002只有1行1-> add： PJ00002 2012 2 0

有没有办法轻松地做到这一点。我现在知道的唯一方法是创建一个类似于：pj_view的视图。然后：

SELECT *
FROM PJ_VIEW
UNION ALL
SELECT t.PROJECT, t.YEAR_NO, 1 AS TYPE_NO, 0 AS AMT
FROM PJ_VIEW t
WHERE NOT EXISTS (SELECT 1 FROM PJ_VIEW t2 WHERE t2.PROJECT = t.PROJECT AND t2.YEAR_NO = t.YEAR_NO AND t2.TYPE_NO = 1)
UNION ALL
SELECT t.PROJECT, t.YEAR_NO, 2 AS TYPE_NO, 0 AS AMT
FROM PJ_VIEW t
WHERE NOT EXISTS (SELECT 1 FROM PJ_VIEW t2 WHERE t2.PROJECT = t.PROJECT AND t2.YEAR_NO = t.YEAR_NO AND t2.TYPE_NO = 2)

Answer 1

对于基于数据库的解决方案，您需要交叉加入。以下解决方案将适用于 n 项目类型。该解决方案需要一个包含默认金额的所有项目类型的表。

首先创建表：

    CREATE TABLE `_projects` (
      `Project`     VARCHAR(20) NOT NULL,
      `_Year`       INT(4) NOT NULL,
      `ProjectType` INT(1) NOT NULL,
      `ProjectAmt`  INT(11) NOT NULL
    );
    CREATE TABLE `_projecttypes` (
      `DefaultType` INT(1) NOT NULL,
      `DefaultAmt`  INT(11) DEFAULT 0
    );

插入您的项目名称，年份，项目类型和金额：

    INSERT INTO `_projects`
    VALUES ('PJ00001', 2012, 1,    1000),
           ('PJ00001', 2012, 2,    1000),
           ('PJ00001', 2011, 1,    1000),
           ('PJ00002', 2012, 1,    1000);

现在添加您的项目类型，默认值为0（零）或您需要的任何值：

    INSERT INTO `_projecttypes`
    VALUES (1,0),(2,0);

这是查询，它将为任何尚未具有默认值的项目添加0的默认值0。

    SELECT 
        IFNULL(`_actualprojects`.`Project`,`der2`.`Project`)          AS `_Project`,
        IFNULL(`_actualprojects`.`_Year`,`der2`.`_Year`)              AS `_Year`,
        IFNULL(`_actualprojects`.`ProjectType`,`der2`.`DefaultType`)  AS `_Type`,
        IFNULL(`_actualprojects`.`ProjectAmt`,`der2`.`DefaultAmt`)    AS `_Amt` 
    FROM 
        (   SELECT DISTINCT `der1`.`DefaultType`,
                            `der1`.`Project`,
                            `der1`.`_Year`,
                            `der1`.`DefaultAmt` 
            FROM 
                (   SELECT *
                    FROM `_projecttypes` CROSS JOIN  `_projects` 
                ) AS `der1`
        ) AS `der2` 
        LEFT JOIN `_projects` AS `_actualprojects` 
            ON `der2`.`Project`       = `_actualprojects`.`Project`
            AND `der2`.`_Year`        = `_actualprojects`.`_Year`
            AND `der2`.`DefaultType`  = `_actualprojects`.`ProjectType`
    ;

如果您想要更多的项目类型，则只需将它们添加到带有默认数量的_projectType表中，然后将上述查询添加到_projectType表中。

尝试一下，添加这些值，然后重新运行上述查询。

    INSERT INTO `_projecttypes`
    VALUES (3,0),(4,0);

---

我希望这对您有帮助。

Answer 2

使用DB2临时表尝试这样的东西。只需用您的查询替换名为查询的表的内容即可。

WITH TypeList AS ( 
    SELECT 1 TypeCode
    UNION ALL
    SELECT 2
),
query AS (
    SELECT Project, [Year], [Type], Amt FROM yourQuerySource
),
DistinctProjectYears AS (
    SELECT Project, [YEAR]
    FROM query
    GROUP BY Project, [YEAR]
),
Projects AS (
    SELECT Project, [YEAR], [Type] as TypeCode, MAX(Amt) as Amt
    FROM query
    GROUP BY Project, [Year], [Type]
)
SELECT l.TypeCode, py.Project, py.[Year], COALESCE(t.Amt, 0) as Amt
FROM TypeList l CROSS JOIN DistinctProjectYears py
    LEFT JOIN Projects t ON l.TypeCode = t.TypeCode
        AND py.Project = t.Project
        AND py.[Year] = t.[Year]