当前位置：首页 > news >正文

SQL笛卡尔积运用-为每个用户初始化数据

news 2025/5/22 12:13:43

需求

假设有个业务需求，是要给每个用户初始化入口。比如“HR系统”，“财务系统”，其他的用户登录后自行添加。

表结构：用户表（sys_user)，模块项表（portal_item）, 用户个性化关联表（关联表）

思路

如果单个用户添加的话，很简单：

INSERT INTO portal_dthn.portal_user_item ("ID", "USER_ID", "ITEM_ID", "ITEM_TYPE_ID", "ORG_ID", "CREATE_BY", "CREATE_NAME", "CREATE_TIME", "UPDATE_BY", "UPDATE_NAME", "UPDATE_TIME", "VERSION", "DEL_FLAG", item_sort) VALUES('4b3dbc5274ce6983186afaba8cf43dee', '1759875209759367169', '100002', '1727141210439102466', '1759874832225869827', 'system', '系统自动创建', '2025-04-25 09:50:20.000', 'system', '系统自动更新', '2025-04-25 09:50:20.000', 1, '0', 0);

如果为多个用户添加单个的话可以用 insert…select

INSERT INTO portal_dthn.portal_user_item 
("ID", "USER_ID", "ITEM_ID", "ITEM_TYPE_ID", "ORG_ID", "CREATE_BY", "CREATE_NAME", "CREATE_TIME", "UPDATE_BY", "UPDATE_NAME", "UPDATE_TIME", "VERSION", "DEL_FLAG", "item_sort")
SELECT -- 生成唯一ID（使用MD5哈希）MD5(u."ID" || items."ITEM_ID" || random()::text) AS "ID",u."ID" AS "USER_ID",'100002' as "ITEM_ID",'1727141210439102466' AS "ITEM_TYPE_ID",  -- 固定值，同示例'1759874832225869827' AS "ORG_ID",       -- 固定值，同示例'system' AS "CREATE_BY",                  -- 示例中使用NULL，但字段不能为空，设为system'系统自动创建' AS "CREATE_NAME",           -- 示例中使用NULL，但字段不能为空CURRENT_TIMESTAMP AS "CREATE_TIME",       -- 示例中使用NULL，但字段不能为空，设为当前时间'system' AS "UPDATE_BY",                  -- 示例中使用NULL，但字段不能为空'系统自动更新' AS "UPDATE_NAME",           -- 示例中使用NULL，但字段不能为空CURRENT_TIMESTAMP AS "UPDATE_TIME",       -- 示例中使用NULL，但字段不能为空1 AS "VERSION",                           -- 固定值，同示例'0' AS "DEL_FLAG",                        -- 固定值，同示例1 AS "item_sort"                         -- 
FROM sys_user u

如果是添加多个系统入口的话，可以把上边的复制几次运行，或者是用 cross_join 笛卡尔积关联，为每个用户，关联多个系统

-- 为所有用户插入6个模块，包含所有必要字段
INSERT INTO portal_dthn.portal_user_item 
("ID", "USER_ID", "ITEM_ID", "ITEM_TYPE_ID", "ORG_ID", "CREATE_BY", "CREATE_NAME", "CREATE_TIME", "UPDATE_BY", "UPDATE_NAME", "UPDATE_TIME", "VERSION", "DEL_FLAG", "item_sort")
SELECT -- 生成唯一ID（使用MD5哈希）MD5(u."ID" || items."ITEM_ID" || random()::text) AS "ID",u."ID" AS "USER_ID",items."ITEM_ID",'1727141210439102466' AS "ITEM_TYPE_ID",  -- 固定值，同示例'1759874832225869827' AS "ORG_ID",       -- 固定值，同示例'system' AS "CREATE_BY",                  -- 示例中使用NULL，但字段不能为空，设为system'系统自动创建' AS "CREATE_NAME",           -- 示例中使用NULL，但字段不能为空CURRENT_TIMESTAMP AS "CREATE_TIME",       -- 示例中使用NULL，但字段不能为空，设为当前时间'system' AS "UPDATE_BY",                  -- 示例中使用NULL，但字段不能为空'系统自动更新' AS "UPDATE_NAME",           -- 示例中使用NULL，但字段不能为空CURRENT_TIMESTAMP AS "UPDATE_TIME",       -- 示例中使用NULL，但字段不能为空1 AS "VERSION",                           -- 固定值，同示例'0' AS "DEL_FLAG",                        -- 固定值，同示例items."item_sort"                         -- 从关联表获取
FROM sys_user u
CROSS JOIN (-- 定义要添加的6个模块SELECT '100002' AS "ITEM_ID", 0 AS "item_sort" UNION ALLSELECT '100013', 1 UNION ALLSELECT '100027', 2 UNION ALLSELECT '100031', 3 UNION ALLSELECT '1762661342671032321', 4 UNION ALLSELECT '1760589482261319682', 5
) AS items
WHERE NOT EXISTS (-- 避免重复插入已存在的记录SELECT 1 FROM portal_dthn.portal_user_item pui WHERE pui."USER_ID" = u."ID" AND pui."ITEM_ID" = items."ITEM_ID"
);