如何在SQL中基于某列内容重复N次填充指定列生成目标数据

核心逻辑是构造固定ITEM_ID集合与(PORTFOLIO_ID, USER_ID)配对集合的笛卡尔积，即可批量生成所需数据。

通用SQL写法（兼容所有主流数据库） #

直接查询生成目标结果 #

SELECT 
  ROW_NUMBER() OVER(ORDER BY p.PORTFOLIO_ID, i.ITEM_ID) AS ID,
  'RESOURCE' AS ITEM_TYPE,
  i.ITEM_ID,
  p.PORTFOLIO_ID,
  p.USER_ID
FROM 
  -- 构造ITEM_ID取值集合，可自行扩展
  (SELECT 11 AS ITEM_ID UNION ALL SELECT 22 UNION ALL SELECT 33) i
CROSS JOIN 
  -- 构造PORTFOLIO_ID、USER_ID配对集合，新增配对只需加UNION ALL即可
  (SELECT 1 AS PORTFOLIO_ID, 1 AS USER_ID UNION ALL SELECT 2, 2) p;

直接插入到assign_item表 #

如果表中ID字段为自增主键，无需手动生成ID，直接执行插入语句即可：

INSERT INTO assign_item (ITEM_TYPE, ITEM_ID, PORTFOLIO_ID, USER_ID)
SELECT 
  'RESOURCE' AS ITEM_TYPE,
  i.ITEM_ID,
  p.PORTFOLIO_ID,
  p.USER_ID
FROM 
  (SELECT 11 AS ITEM_ID UNION ALL SELECT 22 UNION ALL SELECT 33) i
CROSS JOIN 
  (SELECT 1 AS PORTFOLIO_ID, 1 AS USER_ID UNION ALL SELECT 2, 2) p;

简化写法（支持VALUES语法的数据库，如PostgreSQL、MySQL 8.0+、SQL Server） #

INSERT INTO assign_item (ITEM_TYPE, ITEM_ID, PORTFOLIO_ID, USER_ID)
SELECT 'RESOURCE', i.ITEM_ID, p.PORTFOLIO_ID, p.USER_ID
FROM (VALUES (11), (22), (33)) AS i(ITEM_ID)
CROSS JOIN (VALUES (1,1), (2,2)) AS p(PORTFOLIO_ID, USER_ID);

内容的提问来源于stack exchange，提问作者ShaxoLudoz