将含双FULL OUTER JOIN的SQL Server三表查询转换为MySQL适配语句
MySQL适配实现方案
核心实现逻辑拆解
MySQL本身不提供原生FULL OUTER JOIN语法,针对本场景下的三表全维度关联需求,不需要硬套用「LEFT JOIN + RIGHT JOIN 拼接UNION」的单表全连接套路,核心思路是先提取三个表所有关联维度的公共键全集,再基于全集逐表左连匹配指标值,从根源上替代多层全外连接的效果,同时避免多轮拼接带来的行重复、聚合值计算错误问题。
原查询的关联本质是基于7个公共维度(报告期、州、服务商、产品、产品组、组合、渠道),保留三个表所有存在的维度组合,仅在相同维度键匹配成功时关联对应的指标值。原SQL Server写法中第二层全连接的ON条件存在逻辑冗余:当全连接后某一侧表的维度值为NULL时,跨表相等判断会直接失效,导致部分本该保留的行被过滤,适配时需要把这部分逻辑修正为基于公共键的匹配规则。
适配后可直接运行的MySQL代码
WITH all_join_keys AS ( -- 提取三个表所有存在的关联维度键,去重形成全集 SELECT reporting_period, state, servicer, product, product_group, portfolio, channel FROM adhoc_work.Cash UNION -- UNION自动去重,避免键重复 SELECT reporting_period, state, servicer, product, product_group, portfolio, channel FROM adhoc_work.Summary_placed UNION SELECT reporting_period, state, servicer, product, product_group, portfolio, channel FROM adhoc_work.Summary_uy ) SELECT k.reporting_period, k.state, k.servicer, k.product, k.product_group, k.portfolio, k.channel, ca.Gross, ca.Costs, ca.commission, ca.commissionable, s_p.WAVG_placed_numerator, s_p.place_balance AS WAVG_placed_denominator, SUM(s_uy.cum_gross) AS gross_uy_num, SUM(s_uy.cum_netnet) AS netnet_uy_num, SUM(s_uy.cum_commission) AS servicer_uy_num, SUM(s_uy.num_accounts) AS uy_den, ca.costs_recovered FROM all_join_keys k -- 逐表左连匹配对应指标,完全替代全外连接保留所有行的效果 LEFT JOIN adhoc_work.Cash ca ON k.reporting_period = ca.reporting_period AND k.state = ca.state AND k.servicer = ca.servicer AND k.product = ca.product AND k.product_group = ca.product_group AND k.portfolio = ca.portfolio AND k.channel = ca.channel LEFT JOIN adhoc_work.Summary_placed s_p ON k.reporting_period = s_p.reporting_period AND k.state = s_p.state AND k.servicer = s_p.servicer AND k.product = s_p.product AND k.product_group = s_p.product_group AND k.portfolio = s_p.portfolio AND k.channel = s_p.channel LEFT JOIN adhoc_work.Summary_uy s_uy ON k.reporting_period = s_uy.reporting_period AND k.state = s_uy.state AND k.servicer = s_uy.servicer AND k.product = s_uy.product AND k.product_group = s_uy.product_group AND k.portfolio = s_uy.portfolio AND k.channel = s_uy.channel WHERE k.reporting_period BETWEEN DATE_FORMAT(TIMESTAMPADD(MONTH,-13,NOW(3)),'%Y%m%d') AND DATE_FORMAT(NOW(3),'%Y%m%d') GROUP BY k.reporting_period, k.state, k.servicer, k.product, k.product_group, k.portfolio, k.channel, ca.Gross, ca.Costs, ca.commission, ca.commissionable, s_p.WAVG_placed_numerator, s_p.place_balance, ca.costs_recovered;
关键注意事项
- 传统的「多组LEFT/RIGHT JOIN 拼接UNION」写法在三表场景下需要拼接6种关联组合,代码冗余且容易出现关联错位、行重复的问题,导致SUM类聚合值虚高。先抽公共键再左连的写法,能从机制上保证每个维度组合只计算一次,结果和原业务逻辑完全对齐。
- 本写法不需要在ON子句中额外编写跨表排除条件,所有匹配逻辑都是基于公共键集和单表的维度值相等判断,逻辑清晰无冗余。
- 若使用MySQL 5.x版本不支持CTE语法,仅需要把
all_join_keys对应的CTE部分改写为嵌在FROM子句的子查询即可,核心逻辑完全一致。 - 原代码中注释掉的
INTO建表语句,在MySQL中对应的语法为把CREATE TABLE adhoc_work.RevenueReport_Legal AS放在最外层SELECT之前即可使用。
内容的提问来源于stack exchange,提问作者OldJoeClark
相关产品推荐
相关产品推荐

