SQL中INNER JOIN结果集与Metadata表子查询结果的关联合并方案

你当前想拆分结果集再合并的思路不成立的根本原因是：Metadata是典型键值对（EAV）结构，子查询返回的是每个订单对应3行竖版记录，而主查询返回的是每个订单对应1行横版记录，直接关联会触发笛卡尔积，生成重复无效的行，根本匹配不了你要的一行对应一个订单的输出结构。
正确做法是把Metadata中需要用到的payment_method/city/state三个字段提前做行转列，转成和订单ID一一对应的横向列，再嵌入主查询逻辑，不需要拆分两个独立结果集再拼接。
另外你现有SQL和子查询存在几处会导致运行报错的硬编码问题：

• 关联字段名和表结构不匹配：比如customer.customer_id/order.customer_id/order_status.order_id实际对应表结构里的custID/orderID
• 子查询里误写不存在的字段meta_key，实际Metadata表对应字段为key
• 直接使用order作为表名：ORDER是SQL保留关键字，查询时必须加转义标记，否则会触发语法错误

方案1：逐字段关联Metadata表（逻辑简单，适合小数据量场景） #

针对要提取的每个元数据字段，单独关联一次Metadata表，每次关联时限定key的取值，直接取出对应值作为结果列，同时通过CASE语句匹配你期望输出的字段映射规则：

SELECT
  c.custID,
  c.custName AS Name,
  c.email,
  o.orderID,
  os.orderDate,
  -- 匹配期望输出的支付类型映射
  CASE m_pay.value
    WHEN 'cash' THEN 'cash'
    WHEN 'deposit' THEN 'bank'
    WHEN 'cc' THEN 'credit'
  END AS payment,
  -- 匹配期望输出的状态文案映射
  CASE os.status
    WHEN 'paid' THEN 'paid'
    WHEN 'awaiting payment' THEN 'waiting'
    WHEN 'pending' THEN 'pending'
  END AS status,
  m_city.value AS city,
  m_state.value AS state
FROM "Order" o -- 保留字加双引号转义
INNER JOIN Customer c
  ON o.custID = c.custID
INNER JOIN Order_Status os
  ON o.orderID = os.orderID
-- 三次关联Metadata表，分别取需要的元数据字段
LEFT JOIN Metadata m_pay
  ON m_pay.id = o.orderID
  AND m_pay.key = 'payment_method'
LEFT JOIN Metadata m_city
  ON m_city.id = o.orderID
  AND m_city.key = 'city'
LEFT JOIN Metadata m_state
  ON m_state.id = o.orderID
  AND m_state.key = 'state'
WHERE o.productID = 234; -- 匹配测试场景：筛选商品234的所有下单记录

方案2：条件聚合行转列（关联次数少，适合大数据量场景） #

先用CTE把Metadata表需要的元数据字段一次性聚合成横表，再和主查询关联，减少重复扫描Metadata表的开销：

-- 提前把Metadata的键值对转成和订单ID一一对应的横向表
WITH meta_pivot AS (
  SELECT
    id AS orderID,
    MAX(CASE WHEN key = 'payment_method' THEN value END) AS payment_method,
    MAX(CASE WHEN key = 'city' THEN value END) AS city,
    MAX(CASE WHEN key = 'state' THEN value END) AS state
  FROM Metadata
  WHERE key IN ('payment_method', 'city', 'state')
  GROUP BY id
)
SELECT
  c.custID,
  c.custName AS Name,
  c.email,
  o.orderID,
  os.orderDate,
  CASE mp.payment_method
    WHEN 'cash' THEN 'cash'
    WHEN 'deposit' THEN 'bank'
    WHEN 'cc' THEN 'credit'
  END AS payment,
  CASE os.status
    WHEN 'paid' THEN 'paid'
    WHEN 'awaiting payment' THEN 'waiting'
    WHEN 'pending' THEN 'pending'
  END AS status,
  mp.city,
  mp.state
FROM "Order" o
INNER JOIN Customer c
  ON o.custID = c.custID
INNER JOIN Order_Status os
  ON o.orderID = os.orderID
INNER JOIN meta_pivot mp
  ON mp.orderID = o.orderID
WHERE o.productID = 234;

运行后会完全匹配你给出的期望输出结构：

custID | Name  |      email      | orderID | orderDate  | payment | status | city | state
============================================================================================
1      | Sarah | sarah@gmail.com |    12   | 2021-02-01 | cash    | paid   | SF   | LUN
2      | Lyn   | lyn@yahoo.com   |    13   | 2021-02-03 | bank    | waiting| QC   | NCR
3      | John  | john@gmail.com  |    14   | 2021-02-08 | credit  | pending| QC   | NCR

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