时间戳

首页 > 解决方案 > 带有联合子查询的 FULL OUTER JOIN

mysql - 带有联合子查询的 FULL OUTER JOIN

问题描述

我正在尝试使用 mysql 中的 UNION 创建与 FULL OUTER JOIN 具有类似行为的输出

我试图结合的 queires 与不同的时间框架参数相同

例子：

Select * from (
SELECT GGPORTAL as portal, sum(oa.GGAMOUNT) as amount FROM orders as o 
        left orderarticles as oa on o.GGAUFTRAGSNR = oa.GGAUFTRAGSNR 
        where o.GGDATE >= '2020-01-01' and o.GGDATE <= '2020-12-31' 
        group by GGPORTAL
        ) as t1
        LEFT JOIN t2 ON t1.GGPORTAL = t2.GGPORTAL
UNION 
Select * from (
SELECT GGPORTAL as portal, sum(oa.GGAMOUNT) as amount2 FROM orders as o 
        left join orderarticles as oa on o.GGAUFTRAGSNR = oa.GGAUFTRAGSNR 
        where o.GGDATE >= '2019-01-01' and o.GGDATE <= '2019-12-31' 
        group by GGPORTAL
        ) as t2
        RIGHT JOIN t1 ON t1.GGPORTAL = t2.GGPORTAL
        WHERE t1.GGPORTAL IS NULL

错误：

表“server.t2”不存在

单个子查询：

| 门户 | 金额 |
|--------|------------|
| 3 | 250 |
| 4 | 300 |
| 8 | 400 |
| 9 | 500 |
| 10 | 600 |

| 门户 | 金额 |
|--------|------------|
| 1 | 250 |
| 3 | 200 |
| 4 | 350 |
| 8 | 450 |
| 9 | 550 |

期望的结果：

| 门户 | 金额 | 金额2 |
|--------|-----------|------------|
| 1 | 空| 250 |
| 3 | 250 | 200 |
| 4 | 300 | 350 |
| 8 | 400 | 450 |
| 9 | 500 | 550 |
| 10 | 600 | 空|

问题： 正确的语法是什么？PHP 后端是否有更好的选择来组合这些查询的输出？

标签： mysqlsqlsumpivotfull-outer-join

解决方案

您必须在每个联合查询中重复子查询：

SELECT * 
FROM (
  SELECT GGPORTAL portal, SUM(oa.GGAMOUNT) amount 
  FROM orders o LEFT JOIN orderarticles as oa 
  ON o.GGAUFTRAGSNR = oa.GGAUFTRAGSNR 
  WHERE o.GGDATE >= '2020-01-01' and o.GGDATE <= '2020-12-31' 
  GROUP BY GGPORTAL
) t1 LEFT JOIN (
  SELECT GGPORTAL portal, SUM(oa.GGAMOUNT) amount 
  FROM orders o LEFT JOIN orderarticles as oa 
  ON o.GGAUFTRAGSNR = oa.GGAUFTRAGSNR 
  WHERE o.GGDATE >= '2019-01-01' and o.GGDATE <= '2019-12-31' 
  GROUP BY GGPORTAL
) t2 ON t1.GGPORTAL = t2.GGPORTAL
UNION ALL
SELECT * 
FROM (
  SELECT GGPORTAL portal, SUM(oa.GGAMOUNT) amount 
  FROM orders o LEFT JOIN orderarticles as oa 
  ON o.GGAUFTRAGSNR = oa.GGAUFTRAGSNR 
  WHERE o.GGDATE >= '2020-01-01' and o.GGDATE <= '2020-12-31' 
  GROUP BY GGPORTAL
) t1 RIGHT JOIN (
  SELECT GGPORTAL portal, SUM(oa.GGAMOUNT) amount 
  FROM orders o LEFT JOIN orderarticles as oa 
  ON o.GGAUFTRAGSNR = oa.GGAUFTRAGSNR 
  WHERE o.GGDATE >= '2019-01-01' and o.GGDATE <= '2019-12-31' 
  GROUP BY GGPORTAL
) t2 ON t1.GGPORTAL = t2.GGPORTAL
WHERE t1.GGPORTAL IS NULL

如果您使用的是 MySql 8.0+，您可以使用 CTE 简化代码：

WITH 
  cte1 AS (
    SELECT GGPORTAL portal, SUM(oa.GGAMOUNT) amount 
    FROM orders o LEFT JOIN orderarticles as oa 
    ON o.GGAUFTRAGSNR = oa.GGAUFTRAGSNR 
    WHERE o.GGDATE >= '2020-01-01' and o.GGDATE <= '2020-12-31' 
    GROUP BY GGPORTAL
  ),
  cte2 AS (
    SELECT GGPORTAL portal, SUM(oa.GGAMOUNT) amount 
    FROM orders o LEFT JOIN orderarticles as oa 
    ON o.GGAUFTRAGSNR = oa.GGAUFTRAGSNR 
    WHERE o.GGDATE >= '2019-01-01' and o.GGDATE <= '2019-12-31' 
    GROUP BY GGPORTAL
  ) 
SELECT * 
FROM cte1 t1 LEFT JOIN cte2 t2 
ON t1.GGPORTAL = t2.GGPORTAL
UNION ALL
SELECT * 
FROM cte1 t1 RIGHT JOIN cte2 t2 
ON t1.GGPORTAL = t2.GGPORTAL
WHERE t1.GGPORTAL IS NULL

推荐阅读