SQL关联父表与两个独立子表时避免交叉连接的查询方法
问题原因
你直接将父表同时左连两张子表时,没有增加子表之间的关联条件,同一个父ID下两张子表的多行数据会生成笛卡尔积(即M条子表1行 * N条子表2行 = M*N行结果),因此出现不符合预期的交叉连接结果。
实现方案
你需要先将两张子表按Par_ID(父ID)和Key(子表内序号)关联对齐后,再和父表关联,即可得到预期结果。
标准SQL写法(支持FULL OUTER JOIN的数据库,如PostgreSQL、SQL Server、Oracle等)
SELECT p.ID AS Parent_ID, c1.Name AS Child1_Name, c2.Name AS Child2_Name FROM Parent p LEFT JOIN ( SELECT COALESCE(c1.Par_ID, c2.Par_ID) AS Par_ID, COALESCE(c1.`Key`, c2.`Key`) AS sort_key, c1.Name, c2.Name FROM Child1 c1 FULL OUTER JOIN Child2 c2 ON c1.Par_ID = c2.Par_ID AND c1.`Key` = c2.`Key` ) combined ON p.ID = combined.Par_ID ORDER BY Parent_ID, sort_key;
兼容写法(兼容所有支持UNION的数据库,如低版本MySQL)
如果你的数据库不支持全外连接,可以用UNION ALL实现等价逻辑:
SELECT p.ID AS Parent_ID, combined.c1_name AS Child1_Name, combined.c2_name AS Child2_Name FROM Parent p LEFT JOIN ( -- 取出子表1所有记录,匹配对应子表2的同Key数据 SELECT c1.Par_ID, c1.`Key`, c1.Name AS c1_name, c2.Name AS c2_name FROM Child1 c1 LEFT JOIN Child2 c2 ON c1.Par_ID = c2.Par_ID AND c1.`Key` = c2.`Key` UNION ALL -- 补充子表2中不存在于子表1的同父ID同Key的数据 SELECT c2.Par_ID, c2.`Key`, NULL AS c1_name, c2.Name AS c2_name FROM Child2 c2 LEFT JOIN Child1 c1 ON c2.Par_ID = c1.Par_ID AND c2.`Key` = c1.`Key` WHERE c1.Par_ID IS NULL ) combined ON p.ID = combined.Par_ID ORDER BY Parent_ID, combined.`Key`;
注:SQL中
Key是保留字,所以用反引号包裹避免语法报错,不同数据库的保留字转义符可根据实际情况调整。
内容的提问来源于stack exchange,提问作者Jaime Avendaño
相关产品推荐
相关产品推荐

