sqlite基于现有行添加行

Question

我在弄清楚如何将以下内容组合在一起时遇到了问题：

我有一个包含大量列的sqlite表。在这些列中，一列是Name（相同的名称可以显示多次），另一列是Year。我需要检查姓名，并在每个唯一姓名处检查姓名所有行中的所有年份，查找2018年、2019年、2020年和2021年。对于当前名称不存在的这4年中的每一年，都需要使用该名称、原始行名称中的其他列值以及年份中缺少的年份向表中添加新行。其他所有值都需要留空

编辑：

下面是一个数据集示例

|  Name   |   Year   |  Favorite Color|
|  John   |   2018   |  Blue          |
|  John   |   2019   |  Blue          |
|  Jane   |   2020   |  Yellow        |
|  Jane   |   2021   |  Yellow        |

以下是所需的输出：

|  Name   |   Year   |  Favorite Color|
|  John   |   2018   |  Blue          |
|  John   |   2019   |  Blue          |
|  John   |   2020   |  Blue          |
|  John   |   2021   |  Blue          |
|  Jane   |   2018   |  Yellow        |
|  Jane   |   2019   |  Yellow        |
|  Jane   |   2020   |  Yellow        |
|  Jane   |   2021   |  Yellow        |

但我最终得到了更像这样的东西：

|  Name   |   Year   |  Color  |
|  John   |   2018   |  Blue   |
|  John   |   2019   |  Blue   |
|  John   |          |         |
|  John   |          |         |
|  Jane   |          |         |
|  Jane   |          |         |
|  Jane   |   2020   |  Yellow |
|  Jane   |   2021   |  Yellow |

以下是我尝试使用的代码：

            INSERT INTO final (
                
                Name,
                Year,
                Color
                               
            )
            WITH
                years (Year) AS (
                    SELECT DISTINCT
                        Year
                FROM
                    final
                ),
                groupings (Name) AS (
                    SELECT DISTINCT
                        Name
                    FROM
                        final
                ),
                backbone (Year, Name) AS (
                    SELECT
                        years.Year AS Year,
                        groupings.Name AS Name
                    FROM
                        years
                        CROSS JOIN groupings
                    )
                SELECT
        
                    backbone.Name AS Name,
                    backbone.Year,
                    final.Color
  
                    
                FROM
                    backbone
                    LEFT JOIN final USING (
                        Year, Name)

Any help is appreciated.

Answer 1

您可以CROSS将所有年份加入不同的名称/颜色组合，并LEFT加入表格：

INSERT INTO tablename(name, year, favoritecolor)
SELECT n.name, y.year, n.favoritecolor
FROM (SELECT 2018 year UNION ALL SELECT 2019 UNION ALL SELECT 2020 UNION ALL SELECT 2021) y
CROSS JOIN (SELECT DISTINCT name, favoritecolor  FROM tablename) n
LEFT JOIN tablename t ON (t.year, t.name) = (y.year, n.name)
WHERE t.name IS NULL

或使用CTEs：

WITH 
  names(name, favoritecolor) AS (SELECT DISTINCT name, favoritecolor  FROM tablename),
  years(year) AS (VALUES (2018), (2019), (2020), (2021))
INSERT INTO tablename(name, year, favoritecolor)
SELECT n.name, y.year, n.favoritecolor
FROM years y CROSS JOIN names n
LEFT JOIN tablename t ON (t.year, t.name) = (y.year, n.name)
WHERE t.name IS NULL

请参阅demo。

如果只需要表中当前存储的年份，则可以使用递归CTE：

WITH 
  names(name, favoritecolor) AS (SELECT DISTINCT name, favoritecolor  FROM tablename),
  years(year) AS (
    SELECT MIN(year) FROM tablename
    UNION ALL
    SELECT year + 1 FROM years
    WHERE year < (SELECT MAX(year) FROM tablename)
  )
INSERT INTO tablename(name, year, favoritecolor)
SELECT n.name, y.year, n.favoritecolor
FROM years y CROSS JOIN names n
LEFT JOIN tablename t ON (t.year, t.name) = (y.year, n.name)
WHERE t.name IS NULL

请参阅demo。

结果:

^{tb1}$