mysql - 通过 SQL 从模板表中插入缺失的记录

Question

我有以下一组表格用于管理基于纸质文档的过程:

CREATE TABLE TableA (
 `id` int NOT NULL AUTO_INCREMENT, 
 `data` varchar(256),
 `type` varchar(3)
);

TableA 包含相关过程数据(用于业务逻辑)。 type 字段标识我正在管理的“程序”的类型。

CREATE TABLE Templates (
 `id` int NOT NULL AUTO_INCREMENT,
 `type` varchar(3),
 `id_doc_type` int NOT NULL,
);

模板 列出完成特定类型程序所需的所有文档类型。

CREATE TABLE CheckList (
 `id` int NOT NULL AUTO_INCREMENT, 
 `id_doc_type` int NOT NULL,
 `id_tableA` int NOT NULL,
 `filled` tinyint(1) DEFAULT '0'
);

CheckList 应包含一组记录，其中包含TableA 的每条记录要接收的文档类型，filled 字段供用户使用确认接收到指定的文档类型。

CREATE TABLE DocTypes (
 `id` int NOT NULL AUTO_INCREMENT,
 `doc_type` varchar(24)
);

DocTypes 详细描述文档的类型。

希望表名和字段名有助于找到它们的范围。

此类表格的典型内容如下:

TableA:
------------------------------
id   |   data   |    type
------------------------------
0    | data0    |     M
1    | data1    |     M
2    | data2    |     S
3    | data3    |     M
4    | data4    |     S
------------------------------

Templates:
------------------------------
id   |   type   | id_doc_type
------------------------------
0    | M        |     0
1    | M        |     1
2    | M        |     2
3    | S        |     0
4    | S        |     1
------------------------------

CheckList:
----------------------------------------
id   |id_doc_type|  id_tableA  | filled
----------------------------------------
0    | 0         |     0       |  1
1    | 1         |     0       |  1
2    | 2         |     0       |  1
3    | 0         |     2       |  0
4    | 1         |     2       |  0
5    | 0         |     1       |  0
6    | 1         |     1       |  1
----------------------------------------

DocTypes:
------------------------------
id   | doc_type
------------------------------
0    | doc_type 0
1    | doc_type 1
2    | doc_type 2
------------------------------

我写了几个存储过程来管理这些表，但由于某些原因，由于以前的数据管理模型，CheckList 表有一些“漏洞”，就像上面描述的那样:

如您所见，TableA 中只有记录 0、1(指 id 值)在 CheckList 中具有完整的 doc 类型引用集>，记录 2 只有部分文档类型引用集，记录 3、4 完全缺失。

我需要编写一个 SQL 过程来填充所有保留现有数据并避免创建重复数据的内容。

正如我所说，我已经编写了程序。其中我写了一个来填充一个空的 CheckList 部分(即它为 TableA 的完全未引用的记录创建记录集，就像记录 3 和 4)但是我无法弄清楚如何部分恢复记录 2 的情况。

正如评论中所建议的那样，这就是 CheckList 表在程序完成后的样子:

CheckList:
-----------------------------------------
id   |id_doc_type|  id_tableA  | filled
-----------------------------------------
0    | 0         |     0       |  1
1    | 1         |     0       |  1
2    | 2         |     0       |  1
3    | 0         |     2       |  0
4    | 1         |     2       |  0
5    | 0         |     1       |  0
6    | 1         |     1       |  1
7    | 2         |     1       |  0
8    | 0         |     3       |  0
9    | 1         |     3       |  0
10   | 2         |     3       |  0
11   | 0         |     4       |  0
12   | 1         |     4       |  0
-----------------------------------------

这是我编写的 fillup 存储过程的一部分，经过修改以满足我的需要(但无法正常工作):

INSERT INTO CheckList (id_doc_type, id_tableA)
    SELECT id_doc_type, @id_tableA
    FROM Templates 
    WHERE 
        type = @type
        AND NOT (id_doc_type IN (
            SELECT id_doc_type 
            FROM CheckList 
            WHERE    
                id_tableA = @id_tableA));

当然 @id_tableA 和 @type 是我程序的参数，但在这种情况下它们不能使用(或者也许可以，但我们需要一个外部程序迭代tableA 记录)。

我正在使用 MySQL 5.7 作为 ASP.NET 网络应用程序的后端。

Answer 1

考虑一下

insert into checklist (id_doc_type, id_tablea, filled)
  select t.id_doc_type as id_doc_type, a.id as id_tablea, 0 as filled
    from tablea a
    join templates t on a.type = t.type
    left join checklist c on a.id = c.id_tablea and t.id_doc_type = c.id_doc_type
    where c.id is null;