如何用单条SQL查询返回包含子结果集的结果？

没问题！这个需求其实挺常见的，核心就是利用数据库的JSON聚合函数，把每个员工对应的物品记录打包成嵌套的数组结构，一条SQL就能搞定。下面针对主流数据库给你具体示例，先假设我们的表结构是这样的：

• Employee表：包含employee_id（主键）、name、department等字段
• Item表：包含item_id、employee_id（外键关联Employee）、item_name、quantity等字段

MySQL 实现方式 #

用JSON_OBJECT把单条物品记录转成JSON对象，再用JSON_ARRAYAGG把同一员工的所有物品对象聚合成数组：

SELECT
  e.employee_id,
  e.name,
  e.department,
  -- 把每个Item转成JSON对象，再聚合成数组
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'item_id', i.item_id,
      'item_name', i.item_name,
      'quantity', i.quantity
    )
  ) AS items
FROM Employee e
-- LEFT JOIN确保没有物品的员工也会被返回
LEFT JOIN Item i ON e.employee_id = i.employee_id
-- 按员工字段分组，保证每个员工只出一条结果
GROUP BY e.employee_id, e.name, e.department;

PostgreSQL 实现方式 #

PostgreSQL用json_build_object和json_agg组合，还可以用COALESCE处理无物品时的空数组问题：

SELECT
  e.employee_id,
  e.name,
  e.department,
  -- 处理员工无物品时返回空数组而非NULL
  COALESCE(
    json_agg(
      json_build_object(
        'item_id', i.item_id,
        'item_name', i.item_name,
        'quantity', i.quantity
      )
      -- 过滤掉NULL的Item记录（LEFT JOIN产生的）
      FILTER (WHERE i.item_id IS NOT NULL)
    ),
    '[]'::json
  ) AS items
FROM Employee e
LEFT JOIN Item i ON e.employee_id = i.employee_id
GROUP BY e.employee_id, e.name, e.department;

SQL Server 实现方式 #

SQL Server用子查询配合FOR JSON PATH来生成嵌套的JSON数组：

SELECT
  e.employee_id,
  e.name,
  e.department,
  -- 子查询获取当前员工的所有物品，转成JSON数组
  (
    SELECT
      i.item_id,
      i.item_name,
      i.quantity
    FROM Item i
    WHERE i.employee_id = e.employee_id
    FOR JSON PATH -- 自动生成数组格式
  ) AS items
FROM Employee e;

补充说明 #

这些查询返回的items字段都是JSON格式的数组，应用层可以直接解析成嵌套的对象结构。如果你的数据库比较老旧不支持JSON函数，那可能需要在应用层做二次分组处理，但主流数据库现在都支持这种JSON聚合的方式啦。

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