我想使用Union All和内部连接来计算几个数据库的总和。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行中所做的一切 - 留下"台词"。或用" OP"进行换。 - 我收到一条错误消息:
sql错误(1054):未知列" summen.op1opverfahren" in'字段 列表'
...或...
sql错误(1054):未知列" op1opverfahren'in'field List'
...或...
sql错误(1054):未知列'op.op1opverfahren'in'field List'
我的逻辑错误在哪里?
我在这里看到了所有其他参考,但没有找到任何主题来结合联合表的集成(这不应该是问题)。
有人知道我需要更改吗?
您已经分配了别名,因此您可以参考内部列名
SELECT
SUM(summen.`Keine Operation durchgeführt`),
SUM(summen.`Bioenterics Intragastric Ballon (BIB)`),
SUM(summen.`Gastric Banding`),
SUM(summen.`Roux-en-Y Gastric Bypass`),
SUM(summen.`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"),
SUM(op.OP1OPVerfahren = "1"),
SUM(op.OP1OPVerfahren = "2"),
SUM(op.OP1OPVerfahren = "3"),
SUM(op.OP1OPVerfahren LIKE "%")
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
您需要在两个内部查询的选择列表中添加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