时间戳

首页 > 解决方案 > MySQL 复杂查询 - 使用 UNION ALL 和内部 JOIN 对多个数据库求和

mysql - MySQL 复杂查询 - 使用 UNION ALL 和内部 JOIN 对多个数据库求和

问题描述

我想使用 UNION ALL 和内部 JOIN 计算多个数据库的 SUM。MySQL 用户有权访问所有相关数据库。

到目前为止，这是我的 SQL 查询代码：

SELECT
    SUM(summen.OP1OPVerfahren = "0") AS "Keine Operation durchgeführt",
    SUM(summen.OP1OPVerfahren = "1") AS "Bioenterics Intragastric Ballon (BIB)",
    SUM(summen.OP1OPVerfahren = "2") AS "Gastric Banding",
    SUM(summen.OP1OPVerfahren = "3") AS "Roux-en-Y Gastric Bypass",
    SUM(summen.OP1OPVerfahren LIKE "%") AS "Summe"

FROM
(
    SELECT
        SUM(op.OP1OPVerfahren = "0") AS "Keine Operation durchgeführt",
        SUM(op.OP1OPVerfahren = "1") AS "Bioenterics Intragastric Ballon (BIB)",
        SUM(op.OP1OPVerfahren = "2") AS "Gastric Banding",
        SUM(op.OP1OPVerfahren = "3") AS "Roux-en-Y Gastric Bypass",
        SUM(op.OP1OPVerfahren LIKE "%") AS "Summe"
        FROM ods01.dat_patient p
        LEFT OUTER JOIN ods01.dat_optherapie op ON op.PatID = p.ID
        WHERE Testzwecke = 0
        AND p.ID = op.PatID  -- possibly redundant
        AND NOT EXISTS (SELECT 1
                        FROM ods01.dat_optherapie op2
                        WHERE op2.PatID = p.ID AND op2.revision > op.revision)
        GROUP BY OP1OPVerfahren

    UNION ALL

    SELECT
        SUM(op.OP1OPVerfahren = "0") AS "Keine Operation durchgeführt",
        SUM(op.OP1OPVerfahren = "1") AS "Bioenterics Intragastric Ballon (BIB)",
        SUM(op.OP1OPVerfahren = "2") AS "Gastric Banding",
        SUM(op.OP1OPVerfahren = "3") AS "Roux-en-Y Gastric Bypass",
        SUM(op.OP1OPVerfahren LIKE "%") AS "Summe"
        FROM ods02.dat_patient p
        LEFT OUTER JOIN ods02.dat_optherapie op ON op.PatID = p.ID
        WHERE Testzwecke = 0
        AND p.ID = op.PatID  -- possibly redundant
        AND NOT EXISTS (SELECT 1
                        FROM ods02.dat_optherapie op2
                        WHERE op2.PatID = p.ID AND op2.revision > op.revision)
        GROUP BY OP1OPVerfahren
) summen

GROUP BY OP1OPVerfahren

无论我在前 5 行中做什么——离开“summen”，离开“summen”。或将其与“操作”交换。- 我收到一条错误消息：

SQL 错误 (1054)：“字段列表”中的未知列“summen.OP1OPVerfahren”

... 或者 ...

SQL 错误 (1054)：“字段列表”中的未知列“OP1OPVerfahren”

... 或者 ...

SQL 错误 (1054)：“字段列表”中的未知列“op.OP1OPVerfahren”

我的逻辑错误在哪里？

我在这里看到了所有其他关于此的参考资料，但没有找到任何与 JOINed 表的集成相关的主题（这不应该是问题）。

有没有人知道我需要改变什么？

标签： mysqlsqljoinsum

解决方案

您需要在两个内部查询的选择列表中添加 OP1OPVerfahren -

SELECT
    SUM(OP1OPVerfahren = "0") AS "Keine Operation durchgeführt",
    SUM(OP1OPVerfahren = "1") AS "Bioenterics Intragastric Ballon (BIB)",
    SUM(OP1OPVerfahren = "2") AS "Gastric Banding",
    SUM(OP1OPVerfahren = "3") AS "Roux-en-Y Gastric Bypass",
    SUM(OP1OPVerfahren LIKE "%") AS "Summe"

FROM
(
    SELECT OP1OPVerfahren,
        SUM(op.OP1OPVerfahren = "0") AS "Keine Operation durchgeführt",
        SUM(op.OP1OPVerfahren = "1") AS "Bioenterics Intragastric Ballon (BIB)",
        SUM(op.OP1OPVerfahren = "2") AS "Gastric Banding",
        SUM(op.OP1OPVerfahren = "3") AS "Roux-en-Y Gastric Bypass",
        SUM(op.OP1OPVerfahren LIKE "%") AS "Summe"
        FROM ods01.dat_patient p
        LEFT OUTER JOIN ods01.dat_optherapie op ON op.PatID = p.ID
        WHERE Testzwecke = 0
        AND p.ID = op.PatID  -- possibly redundant
        AND NOT EXISTS (SELECT 1
                        FROM ods01.dat_optherapie op2
                        WHERE op2.PatID = p.ID AND op2.revision > op.revision)
        GROUP BY OP1OPVerfahren

    UNION ALL

    SELECT OP1OPVerfahren,
        SUM(op.OP1OPVerfahren = "0") AS "Keine Operation durchgeführt",
        SUM(op.OP1OPVerfahren = "1") AS "Bioenterics Intragastric Ballon (BIB)",
        SUM(op.OP1OPVerfahren = "2") AS "Gastric Banding",
        SUM(op.OP1OPVerfahren = "3") AS "Roux-en-Y Gastric Bypass",
        SUM(op.OP1OPVerfahren LIKE "%") AS "Summe"
        FROM ods02.dat_patient p
        LEFT OUTER JOIN ods02.dat_optherapie op ON op.PatID = p.ID
        WHERE Testzwecke = 0
        AND p.ID = op.PatID  -- possibly redundant
        AND NOT EXISTS (SELECT 1
                        FROM ods02.dat_optherapie op2
                        WHERE op2.PatID = p.ID AND op2.revision > op.revision)
        GROUP BY OP1OPVerfahren
) summen
GROUP BY OP1OPVerfahren

推荐阅读