在PL/SQL中如何将两个关联子记录合并为同一行输出

实现思路 #

• 首先为Srn_Table2中同ID的记录按ADDRESSID升序排序生成行号，区分第1、第2个地址
• 通过条件聚合将同ID的两行地址数据合并为单行
• 最后通过公共字段ID关联Srn_Table1获得最终结果

通用SQL实现（支持MySQL 8.0+/PostgreSQL/SQL Server等支持窗口函数的数据库） #

SELECT 
  t1.ID,
  MAX(CASE WHEN t2.rn = 1 THEN t2.ADDRESS END) AS FIRST_ADDRESS,
  MAX(CASE WHEN t2.rn = 1 THEN t2.ADDRESSID END) AS FIRST_ADDRESSID,
  MAX(CASE WHEN t2.rn = 2 THEN t2.ADDRESS END) AS SECOND_ADDRESS,
  MAX(CASE WHEN t2.rn = 2 THEN t2.ADDRESSID END) AS SECOND_ADDRESSID
FROM Srn_Table1 t1
LEFT JOIN (
  SELECT 
    ID,
    ADDRESS,
    ADDRESSID,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ADDRESSID ASC) AS rn
  FROM Srn_Table2
) t2 ON t1.ID = t2.ID
GROUP BY t1.ID;

兼容MySQL 5.x的实现方案（不支持窗口函数场景） #

如果使用不支持窗口函数的低版本MySQL，可以用Srn_Table2自连接实现：

SELECT 
  t1.ID,
  t2_1.ADDRESS AS FIRST_ADDRESS,
  t2_1.ADDRESSID AS FIRST_ADDRESSID,
  t2_2.ADDRESS AS SECOND_ADDRESS,
  t2_2.ADDRESSID AS SECOND_ADDRESSID
FROM Srn_Table1 t1
LEFT JOIN Srn_Table2 t2_1 ON t1.ID = t2_1.ID AND t2_1.ADDRESSID = (
  SELECT MIN(ADDRESSID) FROM Srn_Table2 WHERE ID = t1.ID
)
LEFT JOIN Srn_Table2 t2_2 ON t1.ID = t2_2.ID AND t2_2.ADDRESSID = (
  SELECT MAX(ADDRESSID) FROM Srn_Table2 WHERE ID = t1.ID
);

结果说明 #

针对提供的示例数据，执行上述查询后返回结果为：

如果需要同时返回客户名称，只需在SELECT字段中添加t1.CUSTOMERNAME即可。

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