创建临时表插入数据遇‘单行子查询返回多行’错误，求高效解法

你的问题出在那两个独立子查询上——它们没有和主查询的ALL_ASSOCIATES_V表做关联，执行时会返回所有满足A.MANAGER_WORKER_ID = B.WORKER_ID的记录，而不是对应主查询当前行的单个结果，自然就触发了“单行子查询返回多行”的错误。

更高效的解决方式是直接用JOIN关联表，代替冗余的子查询，既能保证每行只获取对应的数据，还能提升查询性能。修改后的SQL代码如下：

DROP TABLE IF EXISTS MANAGER_DATA;

CREATE TEMPORARY TABLE MANAGER_DATA (
  WORKER_ID VARCHAR, ACCOUNT VARCHAR,
  ACTIVE Boolean, REPORTING_NAME VARCHAR,
  BUSINESS_TITLE VARCHAR, MANAGER_WORKER_ID VARCHAR, 
  MANAGER_REPORTING_NAME Text, MANAGER_OF_MANAGER_ID VARCHAR,
  MANAGER_OF_MANAGER_NAME VARCHAR);

INSERT INTO MANAGER_DATA ( 
  SELECT 
    main.WORKER_ID,
    main.ACCOUNT, 
    main.ACTIVE, 
    main.REPORTING_NAME, 
    main.BUSINESS_TITLE, 
    main.MANAGER_WORKER_ID,
    manager.REPORTING_NAME AS MANAGER_REPORTING_NAME,
    manager.MANAGER_WORKER_ID AS MANAGER_OF_MANAGER_ID,
    NULL AS MANAGER_OF_MANAGER_NAME
  FROM ALL_ASSOCIATES_V AS main
  LEFT JOIN ALL_ASSOCIATES_V AS manager 
    ON main.MANAGER_WORKER_ID = manager.WORKER_ID
);

SELECT * FROM MANAGER_DATA;

关键修改说明： #

• 给主查询的ALL_ASSOCIATES_V表起别名main，清晰区分主表与关联表
• 通过LEFT JOIN关联同一个表获取经理信息（别名manager），确保每个主表行只匹配对应的经理数据
• 如果需要补全MANAGER_OF_MANAGER_NAME字段，可以再加一层JOIN：

LEFT JOIN ALL_ASSOCIATES_V AS manager_of_manager 
  ON manager.MANAGER_WORKER_ID = manager_of_manager.WORKER_ID

然后在SELECT列表中添加manager_of_manager.REPORTING_NAME AS MANAGER_OF_MANAGER_NAME即可。

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