关联多唯一键表并仅返回单行结果（附import_order表示例）

嘿，针对你的需求，我整理了几个实用的数据库查询方案，帮你实现关联多表且仅返回单行结果的目标！

首先明确你的核心场景：import_order表的复合主键是import_date+import_no+product_id，意味着同一采购单（相同的import_date和import_no）会对应多行产品数据。要关联其他带唯一键的表（比如供应商表suppliers、产品信息表products，甚至支付表payments等）且返回单行，核心思路是按采购单维度聚合产品数据，同时利用唯一键关联的特性避免行数膨胀。

方案1：字符串聚合+基础JOIN（通用SQL兼容） #

适合你想把同批次所有产品信息拼接成一个字段的场景，同时关联唯一键表的信息：

SELECT
  io.import_date,
  io.import_no,
  s.supplier_name, -- 因supplier_id是唯一键，同一采购单的supplier_id唯一，直接取值即可
  -- 拼接产品详情：名称+数量+单价
  STRING_AGG(CONCAT(p.product_name, ' (数量:', io.qty, ', 单价:', io.purchase_cost, ')'), '; ') AS product_list,
  SUM(io.purchase_cost * io.qty) AS total_purchase_amount -- 计算采购单总金额
FROM import_order io
-- 关联供应商表（唯一键supplier_id）
JOIN suppliers s ON io.supplier_id = s.supplier_id
-- 关联产品信息表（唯一键product_id）
JOIN products p ON io.product_id = p.product_id
-- 按采购单维度分组，确保返回单行
GROUP BY io.import_date, io.import_no, s.supplier_name

说明：STRING_AGG是标准SQL的字符串聚合函数，MySQL可用GROUP_CONCAT替代，Oracle可用LISTAGG。因为关联的是唯一键表，JOIN后不会产生额外行数，GROUP BY后每个采购单只会返回一行。

方案2：条件聚合（固定列展示特定产品） #

如果你需要把特定产品的信息拆分成单独的列展示（比如固定显示前N个产品的成本和数量），可以用条件聚合：

SELECT
  import_date,
  import_no,
  supplier_name,
  -- 提取p00001的采购成本和数量
  MAX(CASE WHEN product_id = 'p00001' THEN purchase_cost END) AS p00001_cost,
  MAX(CASE WHEN product_id = 'p00001' THEN qty END) AS p00001_qty,
  -- 提取p00002的采购成本和数量
  MAX(CASE WHEN product_id = 'p00002' THEN purchase_cost END) AS p00002_cost,
  MAX(CASE WHEN product_id = 'p00002' THEN qty END) AS p00002_qty,
  SUM(purchase_cost * qty) AS total_amount
FROM (
  -- 先关联唯一键表，获取基础信息
  SELECT
    io.*,
    s.supplier_name
  FROM import_order io
  JOIN suppliers s ON io.supplier_id = s.supplier_id
) t
GROUP BY import_date, import_no, supplier_name

说明：利用CASE语句将多行产品数据转成单行的多列，因为同一采购单的product_id唯一（复合主键），MAX函数可以准确取出对应产品的数值。

方案3：JSON聚合（灵活保留所有产品数据） #

如果用PostgreSQL、MySQL 8.0+或SQL Server 2016+，可以用JSON聚合把同批次产品数据转成JSON数组，既保持单行结果，又保留完整的产品详情：

SELECT
  io.import_date,
  io.import_no,
  s.supplier_name,
  -- 将产品数据聚合为JSON数组
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'product_id', io.product_id,
      'product_name', p.product_name,
      'purchase_cost', io.purchase_cost,
      'qty', io.qty
    )
  ) AS products_detail,
  SUM(io.purchase_cost * io.qty) AS total_amount
FROM import_order io
JOIN suppliers s ON io.supplier_id = s.supplier_id
JOIN products p ON io.product_id = p.product_id
GROUP BY io.import_date, io.import_no, s.supplier_name

说明：返回的products_detail是一个JSON数组，包含该采购单所有产品的详细信息，非常适合前端直接解析或后续数据处理。

关键注意事项 #

• 因为关联的是带唯一键的表，所以JOIN操作不会产生笛卡尔积，不会导致行数意外增加，这是实现单行结果的前提；
• 分组维度必须是你想要的单行标识（这里是import_date+import_no），确保同一标识的所有行被聚合为一行；
• 如果需要关联更多唯一键表（比如支付表payments，唯一键为import_date+import_no），直接加入JOIN即可，不会影响单行结果：

-- 示例：关联支付表
SELECT
  io.import_date,
  io.import_no,
  s.supplier_name,
  pm.payment_status, -- 支付表的唯一键对应采购单，直接取值
  STRING_AGG(CONCAT(p.product_name, '(', io.qty, ')'), '; ') AS product_list
FROM import_order io
JOIN suppliers s ON io.supplier_id = s.supplier_id
JOIN products p ON io.product_id = p.product_id
JOIN payments pm ON io.import_date = pm.import_date AND io.import_no = pm.import_no
GROUP BY io.import_date, io.import_no, s.supplier_name, pm.payment_status

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