H2数据库从关联子表随机抽数报错，求解决方案

表结构 #

PERSON主表 #

id primary
name
age
address
company

COMP1、COMP2、COMP3子表（结构一致） #

id foreign references PERSON(id)
department

需求 #

从PERSON表获取总计18个姓名，要求：

• 从COMP1中随机选6个department='sales'的人员
• 从COMP2中随机选6个department='production'的人员
• 从COMP3中随机选6个department='supply'的人员

尝试的SQL语句 #

SELECT name AS "Candidate's name" 
FROM PERSON 
WHERE id IN (
  SELECT id FROM COMP1 WHERE department='sales' 
  ORDER BY RANDOM() LIMIT 6 
  UNION
  SELECT id FROM COMP2 WHERE department='production' 
  ORDER BY RANDOM LIMIT 6 
  UNION
  SELECT id FROM COMP3 WHERE department='supply'
  ORDER BY RANDOM() LIMIT 6
);

错误信息 #

org.h2.jdbc.JdbcSQLDataException: Scalar subquery contains more than one row.

问题根源是H2对嵌套子查询的语法解析规则：你给每个子查询单独添加ORDER BY和LIMIT后，H2会将其误判为标量子查询（预期返回单行），从而触发错误。

修正方案1：给每个子查询加括号+使用UNION ALL #

给每个带ORDER BY和LIMIT的子查询套上括号，让H2识别为独立数据集，同时用UNION ALL替代UNION（三个子表的id无重复，效率更高）：

SELECT name AS "Candidate's name"
FROM PERSON
WHERE id IN (
  (SELECT id FROM COMP1 WHERE department='sales' ORDER BY RANDOM() LIMIT 6)
  UNION ALL
  (SELECT id FROM COMP2 WHERE department='production' ORDER BY RANDOM() LIMIT 6)
  UNION ALL
  (SELECT id FROM COMP3 WHERE department='supply' ORDER BY RANDOM() LIMIT 6)
);

修正方案2：用JOIN关联查询 #

通过子查询先获取所有符合条件的id，再关联PERSON表取姓名，逻辑更直观：

SELECT p.name AS "Candidate's name"
FROM (
  (SELECT id FROM COMP1 WHERE department='sales' ORDER BY RANDOM() LIMIT 6)
  UNION ALL
  (SELECT id FROM COMP2 WHERE department='production' ORDER BY RANDOM() LIMIT 6)
  UNION ALL
  (SELECT id FROM COMP3 WHERE department='supply' ORDER BY RANDOM() LIMIT 6)
) AS selected_ids
JOIN PERSON p ON selected_ids.id = p.id;

两种写法都能让H2正确解析每个子查询的随机排序和限制数量，最终合并得到18个符合要求的人员姓名。

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