mysql - 插入到 MySQL 中的另一个表时，使用自动增量连接多个表值

Question

我在 MySQL 中有 2 个表，如下所示：

注意category_id是categories表中category_key的FOREIGN KEY

我想插入到第三个表（名为“目录”）目录项，这样每个目录 ID 都将从它的类别和子类别 ID 生成，并为每个类别-子类别 ID 的串联自动递增编号。

例如：

假设我们在 "categories" 表中有一行，其 category_key 为 "ABC"，并且我们在 "sub_categories" 表中也有一行，其 sub_category_key 为 "EFG"，所以目录表中的一行将有键 "ABC" -EFG-0001”，同一子类别中的另一个将有“ABC-EFG-0002”，依此类推。

例如，对于其他 ID 值。category_id="OMG" 和 sub_category_id="YYY"，数字的增量将从 1 开始，因此 ID 为“OMG-YYY-0001”。

我可以举一个 INSERT 查询的例子吗？

谢谢！

标签： mysqldatabaseselectsql-insertwindow-functions

Answer 1

您可以使用ROW_NUMBER()（在 MySQL 8.0 中可用）来分配数字并LPAD()格式化它们。请注意，您不需要使用 table categories，因为所有相关信息都可以在sub_categories（但如果您确实需要它，您只需添加 a JOIN）。

INSERT INTO catalog
SELECT 
    sc.*,
    CONCAT(
        category_key,
        '-',
        sub_category_key,
        '-',
        LPAD(
            ROW_NUMBER() OVER(PARTITION BY category_key, sub_category_key ORDER BY sub_category_key),
            4,
            0
        ) 
    )
FROM sub_categories sc