Oracle SQL无子查询改写咨询：两查询的可行性与实现方案

问题解答 #

1. 是否可以改写为无任何子查询的形式？
   可以，通过Oracle支持的CROSS JOIN、窗口函数或公共表表达式（WITH子句），能够完全移除所有子查询（包括标量子查询、嵌套子查询），同时保持原查询的逻辑和结果一致。

2. 具体实现方案

查询#1 改写 #

原查询逻辑：获取所有等级（Stufe）高于全局平均等级的角色，同时返回该角色等级和全局平均等级。

方案1：使用CROSS JOIN（完全无嵌套）

SELECT c.Stufe, avg_avg.Stufe_avg
FROM Charaktere c
CROSS JOIN (SELECT AVG(TO_NUMBER(Stufe)) AS Stufe_avg FROM Charaktere) avg_avg
WHERE TO_NUMBER(c.Stufe) > avg_avg.Stufe_avg
ORDER BY c.Stufe DESC;

方案2：使用窗口函数（简化逻辑）

SELECT Stufe, Stufe_avg
FROM (
    SELECT 
        Stufe,
        AVG(TO_NUMBER(Stufe)) OVER () AS Stufe_avg
    FROM Charaktere
)
WHERE TO_NUMBER(Stufe) > Stufe_avg
ORDER BY Stufe DESC;

注：此处内层仅为窗口函数的逻辑封装，不属于传统过滤/分组类子查询；原表中Stufe为字符串类型，计算前需用TO_NUMBER转换为数值类型，避免排序或计算错误。

查询#2 改写 #

原查询逻辑：关联角色表和职业表计算角色生命值，关联职业对应的总伤害，筛选生命值减去总伤害后大于0的角色，返回角色名和剩余生命值。

方案1：直接JOIN分组结果

SELECT 
    c.Name,
    (TO_NUMBER(k.Basisleben) * TO_NUMBER(c.Leben_Multiplikator)) - TO_NUMBER(e.Gruppenschaden) AS Zustand
FROM Charaktere c
INNER JOIN Klassen k ON c.Klasse = k.Klasse
INNER JOIN (
    SELECT Klasse, SUM(TO_NUMBER(Schaden)) AS Gruppenschaden 
    FROM Charaktere 
    GROUP BY Klasse
) e ON k.Schwächen = e.Klasse
WHERE (TO_NUMBER(k.Basisleben) * TO_NUMBER(c.Leben_Multiplikator)) - TO_NUMBER(e.Gruppenschaden) > 0;

方案2：使用WITH子句（完全消除嵌套结构）

WITH KlasseSchaden AS (
    SELECT Klasse, SUM(TO_NUMBER(Schaden)) AS Gruppenschaden 
    FROM Charaktere 
    GROUP BY Klasse
)
SELECT 
    c.Name,
    (TO_NUMBER(k.Basisleben) * TO_NUMBER(c.Leben_Multiplikator)) - TO_NUMBER(ks.Gruppenschaden) AS Zustand
FROM Charaktere c
INNER JOIN Klassen k ON c.Klasse = k.Klasse
INNER JOIN KlasseSchaden ks ON k.Schwächen = ks.Klasse
WHERE (TO_NUMBER(k.Basisleben) * TO_NUMBER(c.Leben_Multiplikator)) - TO_NUMBER(ks.Gruppenschaden) > 0;

注：原表中Basisleben、Leben_Multiplikator、Schaden均为字符串类型，计算前需转换为数值类型，避免运算错误。

测试数据 #

CREATE TABLE Charaktere (
    Charakter_ID varchar(300),
    Name varchar(300),
    Klasse varchar(300),
    Rasse varchar(300),
    Stufe varchar(300),
    Leben_Multiplikator varchar(300),
    Mana_Multiplikator varchar(300),
    Rüstung varchar(300),
    Waffen_ID varchar(300),
    Schaden varchar(300)
);
CREATE TABLE Klassen (
    Klassen_ID varchar(300),
    Klasse varchar(300),
    Basisleben varchar(300),
    Basismana varchar(300),
    Schwächen varchar(300)
);
CREATE TABLE Ausrüstung (
    Ausrüstung_ID varchar(300),
    Rüstung varchar(300),
    Schmuck varchar(300)
);
CREATE TABLE Waffen (
    Waffen_ID varchar(300),
    Links varchar(300),
    Rechts varchar(300)
);
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('1','Herald','Zauberer','Mensch','67','2','8','Heilig','4','718');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('2','Roderic','Paladin','Mensch','55','10','3','Schwer','2','691');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('3','Favian','Schurke','Ork','32','4','1','Leicht','3','243');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('4','Vega','Berserker','Zwerg','44','9','8','Schwer','2','118');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('5','Matep','Jäger','Dunkel Elf','24','3','6','Leicht','1','368');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('6','Euris','Kleriker','Mensch','77','7','8','Resistent','4','774');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('7','Dara’a','Nekromant','Blut Elf','99','6','1','Verdorben','5','966');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('8','Eodriel','Magier','Hoch Elf','24','2','3','Resistent','5','399');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('9','Kerodan','Magier','Blut Elf','20','6','2','Heilig','4','758');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('10','Hans','Paladin','Mensch','67','7','9','Schwer','2','632');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('11','Falk','Berserker','Mensch','13','8','6','Leicht','2','149');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('12','Sethrak','Paladin','Ork','54','5','1','Schwer','3','657');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('13','Hozen','Kleriker','Zwerg','68','6','3','Heilig','4','710');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('14','Venthyr','Jäger','Dunkel Elf','23','4','7','Leicht','1','197');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('15','Stanford','Paladin','Mensch','56','3','7','Resistent','2','370');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('16','Celoevalin','Zauberer','Blut Elf','8','3','6','Heilig','4','383');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('17','Sylvar','Berserker','Hoch Elf','76','9','4','Verdorben','2','837');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('18','Kyrian','Zauberer','Zwerg','69','6','3','Heilig','5','756');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('19','Ithris','Kleriker','Dunkel Elf','88','9','6','Resistent','4','500');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('20','Diedrich','Magier','Mensch','1','2','2','Heilig','2','102');
INSERT INTO Charaktere (Charakter_ID,Name,Klasse,Rasse,Stufe,Leben_Multiplikator,Mana_Multiplikator,Rüstung,Waffen_ID,Schaden) VALUES ('21','Dar’mir','Jäger','Blut Elf','14','1','7','Leicht','1','150');

INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('1','Zauberer','70','170','Paladin');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('2','Paladin','150','110','Zauberer');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('3','Schurke','100','100','Magier');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('4','Berserker','200','80','Jäger');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('5','Jäger','110','100','Schurke');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('6','Kleriker','95','120','Nekromant');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('7','Nekromant','50','200','Paladin');
INSERT INTO Klassen (Klassen_ID,Klasse,Basisleben,Basismana,Schwächen) VALUES ('8','Magier','85','150','Berserker');

INSERT INTO Ausrüstung (Ausrüstung_ID,Rüstung,Schmuck) VALUES ('1','Schwer','Kette');
INSERT INTO Ausrüstung (Ausrüstung_ID,Rüstung,Schmuck) VALUES ('2','Leicht','Armreif');
INSERT INTO Ausrüstung (Ausrüstung_ID,Rüstung,Schmuck) VALUES ('3','Resistent','Anhänger');
INSERT INTO Ausrüstung (Ausrüstung_ID,Rüstung,Schmuck) VALUES ('4','Heilig','Ring');
INSERT INTO Ausrüstung (Ausrüstung_ID,Rüstung,Schmuck) VALUES ('5','Verdorben','Talisman');

INSERT INTO Waffen (Waffen_ID,Links,Rechts) VALUES ('1','Bogen','Dolch');
INSERT INTO Waffen (Waffen_ID,Links,Rechts) VALUES ('2','Langschwert',NULL);
INSERT INTO Waffen (Waffen_ID,Links,Rechts) VALUES ('3','Axt','Axt');
INSERT INTO Waffen (Waffen_ID,Links,Rechts) VALUES ('4','Zauberstab','Zauberbuch');
INSERT INTO Waffen (Waffen_ID,Links,Rechts) VALUES ('5','Zauberbuch','Zauberbuch');

内容的提问来源于stack exchange，提问作者Philip Gertsch