sql server用for xml auto需父表先于子表在from中出现且子表别名体现层级，postgresql依赖json_agg()聚合，mysql需group by配合json_arrayagg；复杂嵌套推荐应用层组装而非sql硬编码。
SQL Server 用 FOR XML AUTO + JOIN 生成嵌套 XML
SQL Server 原生支持通过 FOR XML 将 JOIN 结果转为嵌套结构，但关键在于表别名和 JOIN 顺序必须体现父子关系。父表必须在 FROM 中先出现，子表用 JOIN 关联，且子表别名需带层级语义（如 child），否则 AUTO 模式无法自动推导嵌套。
常见错误是把子表放 FROM、父表放 JOIN，结果 XML 里子元素变成顶层节点；或漏写别名，导致字段名冲突、嵌套失效。
-
SELECT p.id, p.name, c.id AS 'c_id', c.title FROM parent p JOIN child c ON p.id = c.parent_id FOR XML AUTO, ROOT('data')——c.title会归入<c></c>节点下，前提是别名c被识别为子实体 - 若需更精确控制（比如子节点叫
items而非表名），改用FOR XML EXPLICIT或拼接FOR XML PATH，但复杂度陡增 -
FOR XML AUTO不支持数组空值省略，NULL 字段仍会输出nil="true"属性，前端解析需兼容
PostgreSQL 用 row_to_json + JOIN 构建 JSON 对象嵌套
PostgreSQL 没有内置嵌套 JSON JOIN 语法，必须手动用 row_to_json() 和子查询组合。直接 JOIN 后套 row_to_json() 只能得到扁平对象，真正嵌套得靠聚合：父行配一个子记录数组。
典型场景是“一个订单多个商品”，不能只写 SELECT row_to_json(t) FROM (SELECT o.id, o.status, i.name FROM orders o JOIN items i ON o.id = i.order_id) t——这会每条子记录生成一个独立 JSON，不是嵌套。
- 正确做法：用
json_agg()聚合子表，配合LATERAL或子查询，例如：SELECT row_to_json(parent_rec) FROM (SELECT o.id, o.status, (SELECT json_agg(row_to_json(i)) FROM items i WHERE i.order_id = o.id) AS items FROM orders o) parent_rec - 注意
json_agg()对空子集返回NULL，不是空数组[]，如需空数组得补COALESCE(..., '[]'::json) - 性能上，
LATERAL子查询比关联子查询更易优化，尤其子表数据量大时
MySQL 8.0+ 用 JSON_OBJECT + JSON_ARRAYAGG 实现 JOIN 嵌套
MySQL 8.0 引入了 JSON_OBJECT 和 JSON_ARRAYAGG，但它们不直接响应 JOIN，而是依赖 GROUP BY 将父行与子集绑定。没 GROUP BY 就无法聚合子记录，结果要么报错，要么只取第一条子记录。
容易踩的坑是忘记 GROUP BY 父表主键，或在 SELECT 里混用非聚合字段和聚合函数，触发 sql_mode=only_full_group_by 报错。
- 基础结构：
SELECT JSON_OBJECT('id', p.id, 'name', p.name, 'children', JSON_ARRAYAGG(JSON_OBJECT('id', c.id, 'desc', c.desc))) FROM parent p LEFT JOIN child c ON p.id = c.parent_id GROUP BY p.id - 用
LEFT JOIN保证无子记录的父项不被过滤，此时JSON_ARRAYAGG返回NULL，可用IFNULL(..., JSON_ARRAY())转为空数组 -
JSON_ARRAYAGG默认去重，如果子表有重复字段需加DISTINCT，但通常应由业务逻辑保证
跨数据库通用思路：别依赖 SQL 直出嵌套结构
真正复杂的嵌套（多层、条件过滤、分页）在 SQL 层硬做，很快会变成难以维护的长查询。比如三层嵌套 JSON，MySQL 需三层子查询嵌套，PostgreSQL 要两层 LATERAL，SQL Server 的 EXPLICIT 几乎不可读。
更稳的做法是：SQL 只做单层 JOIN 拉取所有必要字段（含父 ID），在应用层用语言原生结构（如 Python dict/list、Go struct）组装嵌套。这样逻辑清晰、可调试、易加缓存，也避免数据库成为 JSON 渲染瓶颈。
尤其是当嵌套字段需运行时计算（如格式化时间、权限过滤子项）、或要对接 OpenAPI 规范时，SQL 层生成的 JSON 往往不够灵活——那部分工作本就不该由数据库承担。
