时间戳

首页 > 解决方案 > 将三表 FULL OUTER JOIN 查询转换为 MySQL

mysql - 将三表 FULL OUTER JOIN 查询转换为 MySQL

问题描述

我需要将这个在 SQL Server 中工作的查询翻译成 MySQL。我已经绕着它转了至少 50 次，我很困惑，甚至找不到起点。

简而言之就是这些问题：

SELECT COALESCE(ca.reporting_period, s_p.reporting_period, s_uy.reporting_period) AS reporting_period,
       COALESCE(ca.state, s_p.state, s_uy.state) AS state,
       COALESCE(ca.servicer, s_p.servicer, s_uy.servicer) AS servicer,
       COALESCE(ca.product, s_p.product, s_uy.product) AS product,
       COALESCE(ca.product_group, s_p.product_group, s_uy.product_group) AS product_group,
       COALESCE(ca.portfolio, s_p.portfolio, s_uy.portfolio) AS portfolio,
       COALESCE(ca.channel, s_p.channel, s_uy.channel) AS 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
# INTO adhoc_work.RevenueReport_Legal
FROM   adhoc_work.Cash ca
FULL OUTER JOIN adhoc_work.Summary_placed s_p
 ON  ca.reporting_period = s_p.reporting_period
  AND ca.state = s_p.state
  AND ca.servicer = s_p.servicer
  AND ca.product = s_p.product
  AND ca.product_group = s_p.product_group
  AND ca.portfolio = s_p.portfolio
  AND ca.channel = s_p.channel
FULL OUTER JOIN adhoc_work.Summary_uy s_uy
 ON  ca.reporting_period = s_uy.reporting_period
  AND ca.state = s_uy.state
  AND s_p.state = s_uy.state
  AND ca.servicer = s_uy.servicer
  AND s_p.servicer = s_uy.servicer
  AND ca.product = s_uy.product
  AND s_p.product = s_uy.product
  AND ca.product_group = s_uy.product_group
  AND s_p.product_group = s_uy.product_group
  AND ca.portfolio = s_uy.portfolio
  AND s_p.portfolio = s_uy.portfolio
  AND ca.channel = s_uy.channel
  AND s_p.channel = s_uy.channel
WHERE  COALESCE(ca.reporting_period,s_p.reporting_period,s_uy.reporting_period) BETWEEN DATE_FORMAT(TIMESTAMPADD(MONTH,-13,NOW(3)),'%Y%m%d') AND DATE_FORMAT(NOW(3),'%Y%m%d')
GROUP BY COALESCE(ca.reporting_period,s_p.reporting_period,s_uy.reporting_period)
   ,COALESCE(ca.state,s_p.state,s_uy.state)
   ,COALESCE(ca.servicer,s_p.servicer,s_uy.servicer)
   ,COALESCE(ca.product,s_p.product,s_uy.product)
   ,COALESCE(ca.product_group,s_p.product_group,s_uy.product_group)
   ,COALESCE(ca.portfolio,s_p.portfolio,s_uy.portfolio)
   ,COALESCE(ca.channel,s_p.channel,s_uy.channel)
   ,ca.gross
   ,ca.Costs
   ,ca.commission
   ,ca.commissionable
   ,s_p.WAVG_placed_numerator
   ,s_p.place_balance
   ,ca.costs_recovered;

标签： mysqlsqlsql-server

解决方案

实际上，您的查询并没有完全执行完整的外部联接，因为这样的条件：

ca.state = s_uy.state

要求ca.state匹配。也就是说，将外部连接转换为内部连接。

因此，从理论上回答您的问题，最简单的方法可能是通过UNION在表中使用生成所有行，然后使用left join：

FROM (SELECT reporting_period, . . .
      FROM adhoc_work.Cash ca
      UNION   -- on purpose to remove duplicates
      SELECT reporting_period, . . .
      FROM adhoc_work.Summary_placed
      UNION   -- on purpose to remove duplicates
      SELECT reporting_period, . . .
      FROM adhoc_work.Summary_uy
     ) x LEFT JOIN
     adhoc_work.Cash ca
     ON x.reporting_period = ca.reporting_period AND
        . . . LEFT JOIN
     adhoc_work.Summary_placed s_p
     ON x.reporting_period = s_p.reporting_period AND
        . . . LEFT JOIN
     adhoc_work.Summary_uy s_uy
     ON x.reporting_period = s_uy.reporting_period AND
        . . .

推荐阅读