SQL Server关联两表返回无冗余非表格格式数据的实现方案

方案1：JSON/XML聚合（主流数据库优先推荐） #

目前MySQL 5.7+、PostgreSQL 9.4+、SQL Server 2016+等主流数据库都支持原生的JSON聚合函数，可以直接在SQL层组装成你需要的嵌套结构，一次查询返回结果，完全没有Name、Description字段的冗余传输。
假设你的两张表分别为items（存物料基础信息）、item_batches（存批次库存信息），各数据库的参考写法如下：

MySQL示例 #

SELECT 
  i.Id,
  i.Name,
  i.Description,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'Batch', b.Batch,
      'Quantity', b.Quantity
    )
  ) AS Quantities
FROM items i
LEFT JOIN item_batches b ON i.Id = b.Id
GROUP BY i.Id, i.Name, i.Description;

返回结果中每个物料Id仅对应一行，Quantities字段为该物料所有批次的JSON数组，应用层直接反序列化即可得到你需要的Item对象结构。

PostgreSQL示例 #

SELECT 
  i.Id,
  i.Name,
  i.Description,
  json_agg(
    json_build_object(
      'Batch', b.Batch,
      'Quantity', b.Quantity
    )
  ) AS Quantities
FROM items i
LEFT JOIN item_batches b ON i.Id = b.Id
GROUP BY i.Id, i.Name, i.Description;

SQL Server示例 #

SELECT 
  i.Id,
  i.Name,
  i.Description,
  (
    SELECT Batch, Quantity 
    FROM item_batches b 
    WHERE b.Id = i.Id 
    FOR JSON PATH
  ) AS Quantities
FROM items i
FOR JSON PATH, ROOT('Items')

该写法可以直接返回完整的嵌套JSON结构，外层自动包含Items根节点，应用层无需额外组装直接解析即可。 #

方案2：排序后单次查询（兼容老版本数据库） #

如果你使用的数据库不支持JSON聚合，可以用排序+单次关联查询的方案，仅需要一次数据库调用，应用层处理逻辑非常简单：

SELECT 
  i.Id,
  i.Name,
  i.Description,
  b.Batch,
  b.Quantity
FROM items i
LEFT JOIN item_batches b ON i.Id = b.Id
ORDER BY i.Id;

应用层遍历结果时，仅需要判断当前行的Id是否和上一行一致：

• 一致则将当前批次信息追加到当前Item的Quantities列表
• 不一致则新建Item对象存储当前物料的基础信息和批次
  该方案比两次分别查询再映射的逻辑简单很多，代码复杂度极低。

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