时间戳

首页 > 解决方案 > mysql - 公用表表达式：模拟全连接

mysql - mysql - 公用表表达式：模拟全连接

问题描述

我有多个（超过 20 个）cte，我需要将它们连接在一起以并排输出行。

WITH 
cte_lnz AS (SELECT n.name, ROW_NUMBER() OVER (ORDER BY e.id) AS rn_lnz FROM entries e LEFT JOIN nodes n ON n.id = e.node_id LEFT JOIN attribs a ON a.id = e.attrib_id 
WHERE a.name = 'LOCATION' AND e.value = 'Gruberstrasse' AND DATE(ts) = CURRENT_DATE ORDER BY e.id         
), 
cte_wbg AS (SELECT n.name, ROW_NUMBER() OVER (ORDER BY e.id) AS rn_wbg FROM entries e LEFT JOIN nodes n ON n.id = e.node_id LEFT JOIN attribs a ON a.id = e.attrib_id
WHERE  a.name = 'LOCATION' AND e.value = 'Wienerberg' AND DATE(ts) = CURRENT_DATE ORDER BY e.id         
), 
cte_gbg AS ( SELECT n.name, ROW_NUMBER() OVER (ORDER BY e.id) AS rn_gbg FROM entries e LEFT JOIN nodes n ON n.id = e.node_id LEFT JOIN attribs a ON a.id = e.attrib_id
WHERE  a.name = 'LOCATION' AND e.value = 'Geiselberg' AND DATE(ts) = CURRENT_DATE ORDER BY e.id        
),
cte_vie AS ( SELECT n.name, ROW_NUMBER() OVER (ORDER BY e.id) AS rn_vie FROM entries e LEFT JOIN nodes n ON n.id = e.node_id LEFT JOIN attribs a ON a.id = e.attrib_id
WHERE  a.name = 'LOCATION' AND e.value IN ('Geiselberg', 'Wienerberg') AND DATE(ts) = CURRENT_DATE ORDER BY e.id         
)

这适用于前两个 cte ......

-- simulated full join
    SELECT lnz.name LNZ, wbg.name WBG
    FROM cte_lnz lnz LEFT JOIN cte_wbg wbg
    ON wbg.rn_wbg = lnz.rn_lnz
    UNION
    SELECT lnz.name LNZ, wbg.name WBG
    FROM cte_wbg wbg LEFT JOIN cte_lnz lnz
    ON wbg.rn_wbg = lnz.rn_lnz

+----------------------+------------------------------+
| LNZ                  | WBG                          |
+----------------------+------------------------------+
| STP17T1_SGKKT1       | AIXHVBDBT                    |
| AIXSAGRU3            | KUG0167_JBOSS-T4             |
| ARR5S1P9_TIC         | KUG0169_RMAN_HA              |
| AIXBUILDHOST         | KUG0121_ZPVTT1               |

这给出了一个错误......

    SELECT lnz.name LNZ, wbg.name WBG
    FROM cte_lnz lnz LEFT JOIN cte_wbg wbg
    ON wbg.rn_wbg = lnz.rn_lnz
    UNION
    SELECT lnz.name LNZ, wbg.name WBG
    FROM cte_wbg wbg LEFT JOIN cte_lnz lnz
    ON wbg.rn_wbg = lnz.rn_lnz
-- just a placeholder
    SELECT gbg.name GBG, vie.name VIE
    FROM cte_gbg gbg LEFT JOIN cte_vie vie
    ON vie.rn_vie = gbg.rn_gbg
    UNION
    SELECT gbg.name GBG, vie.name VIE
    FROM cte_vie vie LEFT JOIN cte_gbg gbg
    ON vie.rn_vie = gbg.rn_gbg

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT gbg.name GBG, vie.name VIE
FROM cte_gbg gbg LEFT JOIN cte_vie vie
ON vie.' at line 53

那么如何将其他 cte 添加到游戏中？

标签： mysqlsqlcommon-table-expression

解决方案

union完全连接的近似值仍然存在吗？这是一种糟糕的方法——在功能上并不完全等同，而且比必要的要贵得多。

对于您的问题，使用union all和聚合。一种方法是：

SELECT MAX(CASE WHEN which = 'lnz' THEN name END) as lnz,
       MAX(CASE WHEN which = 'wbg' THEN name END) as wbg,
       . . .
FROM ((SELECT 'lnz' as which, rn, lnz.name
       FROM cte_lnz lnz 
      ) UNION ALL
      (SELECT 'wbg', rn, wbg.name
       FROM cte_wbg wbg
      ) UNION ALL
      . . .
     ) n
GROUP BY rn;

添加更多 CTE 就像在子查询中添加更多UNION ALL子句然后MAX(CASE)在外部查询中添加更多表达式一样简单。

推荐阅读