Запрос без использования подзапроса/подзапроса

У меня есть 2 вопроса, касающихся перезаписи запросов, чтобы не требовался подзапрос.

  1. Можно ли, используя операторы Oracle-SQL, написать один из следующих запросов без использования какого-либо типа подзапроса (например, подзапроса)?
  2. Если да, то как это возможно.

Запрос №1 выглядит следующим образом:

SELECT Stufe, (SELECT AVG(Stufe) FROM Charaktere) FROM Charaktere
WHERE Stufe > (SELECT AVG(Stufe) FROM Charaktere) ORDER BY Stufe DESC

Запрос №2 выглядит следующим образом:

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

Данные испытаний:

`

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');

`

Я уже искал в StackOverflow, но не могу переписать эти два запроса на основе предоставленной информации. Думаю, будет достаточно использования JOINS и/или GROUP BY, ...?

Шаблоны Angular PrimeNg
Шаблоны Angular PrimeNg
Как привнести проверку типов в наши шаблоны Angular, использующие компоненты библиотеки PrimeNg, и настроить их отображение с помощью встроенной...
Создайте ползком, похожим на звездные войны, с помощью CSS и Javascript
Создайте ползком, похожим на звездные войны, с помощью CSS и Javascript
Если вы веб-разработчик (или хотите им стать), то вы наверняка гик и вам нравятся "Звездные войны". А как бы вы хотели, чтобы фоном для вашего...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Начала с розового дизайна
Начала с розового дизайна
Pink Design - это система дизайна Appwrite с открытым исходным кодом для создания последовательных и многократно используемых пользовательских...
Шлюз в PHP
Шлюз в PHP
API-шлюз (AG) - это сервер, который действует как единая точка входа для набора микросервисов.
14 Задание: Типы данных и структуры данных Python для DevOps
14 Задание: Типы данных и структуры данных Python для DevOps
проверить тип данных используемой переменной, мы можем просто написать: your_variable=100
0
0
69
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вы можете использовать CTE, чтобы упростить задачу.

Первый запрос можно переписать так:

with cte as (SELECT AVG(Stufe) avg_Stufe FROM Charaktere) 
SELECT Stufe, cte.avg_Stufe
FROM Charaktere 
CROSS JOIN cte 
WHERE Stufe > cte.avg_Stufe 
ORDER BY Stufe DESC

И второй запрос можно переписать как

with cte as (
  SELECT Klasse, SUM(Schaden) as Gruppenschaden  FROM Charaktere  GROUP BY Klasse) 
SELECT c.Name, (k.Basisleben * c.Leben_Multiplikator) - Gruppenschaden as Zustand 
FROM Charaktere c 
JOIN Klassen k ON c.Klasse = k.Klasse
JOIN cte ON (cte.Klasse = k.Schwächen)
WHERE (k.Basisleben * c.Leben_Multiplikator) - Gruppenschaden > 0

Без использования CTE :

Вышеупомянутые 2 запроса можно изменить, чтобы избежать CTE

запрос 1:

SELECT c.Stufe, AVG(cte.Stufe) avg_Stufe
FROM Charaktere c
CROSS JOIN Charaktere cte 
GROUP BY c.Stufe,c.Charakter_ID
HAVING c.Stufe > AVG(cte.Stufe)
ORDER BY c.Stufe DESC

запрос 2:

SELECT c.Name, (k.Basisleben * c.Leben_Multiplikator) - SUM(cte.Schaden) as Zustand 
FROM Charaktere c 
JOIN Klassen k ON c.Klasse = k.Klasse
JOIN Charaktere cte ON (cte.Klasse = k.Schwächen)
group by c.Name, k.Basisleben, c.Leben_Multiplikator
having (k.Basisleben * c.Leben_Multiplikator) - SUM(cte.Schaden) > 0

если вы присмотритесь, вы увидите, что внешние таблицы из более ранних запросов теперь являются объединяющей таблицей.

Спасибо, an33sh! Есть ли способ сделать это без КТ? CTE как бы "выглядит" как подзапрос, так как в нем есть 2 SELECT-ключевых слова.

Philip Gertsch 21.11.2022 14:20

@PhilipGertsch - я обновил свой ответ, чтобы избежать каких-либо запросов на выбор. Пожалуйста, взгляните.

an33sh 21.11.2022 19:17

@an33sh — вы пропустили одну строку в обновленном ответе на запрос 1. В таблице Charaktere есть две строки со Stufe = 67 (CHARAKTER_ID = 1 AND CHARAKTER_ID = 10). С уважением...

d r 21.11.2022 19:43

@PhilipGertsch, исправил это и обновил ответ. Включено c.Charakter_ID также в групповое предложение

an33sh 21.11.2022 20:55

@an33sh Спасибо за обновление - я принял ваш ответ!

Philip Gertsch 21.11.2022 21:07

Ну, CTE — это своего рода цитата подзапроса из docs
Общие табличные выражения - Чтобы указать общие табличные выражения, используйте предложение WITH с одним или несколькими подразделами, разделенными запятыми. Каждый подпункт предоставляет подзапрос, который создает набор результатов и связывает имя с подзапросом. Ваш запрос 1 может быть закодирован без подзапроса, как здесь:

SELECT 
  c0.Stufe,
  Avg(c1.Stufe)  "StufeAVG"
FROM Charaktere c0
LEFT JOIN Charaktere c1 ON(1=1)
GROUP BY c0.Stufe, c0.CHARAKTER_ID
HAVING AVG(c1.Stufe) < c0.Stufe
ORDER BY c0.Stufe DESC
--  
--  R e s u l t :
--
--  Stufe                                    StufeAVG
--  -----   -----------------------------------------   
--     99   46.61904761904761904761904761904761904762
--     88   46.61904761904761904761904761904761904762
--     77   46.61904761904761904761904761904761904762
--     76   46.61904761904761904761904761904761904762
--     69   46.61904761904761904761904761904761904762
--     68   46.61904761904761904761904761904761904762
--     67   46.61904761904761904761904761904761904762
--     67   46.61904761904761904761904761904761904762
--     56   46.61904761904761904761904761904761904762
--     55   46.61904761904761904761904761904761904762
--     54   46.61904761904761904761904761904761904762

... не уверен насчет Query 2, но постараюсь разобраться. Может быть...

Запрос 2 без подзапросов:

SELECT DISTINCT
    c.Name,
    (k.Basisleben * c.Leben_Multiplikator) - Sum(c1.Schaden) "Zustand"
FROM 
    Charaktere c
LEFT JOIN 
    Klassen k ON(c.Klasse = k.Klasse)
LEFT JOIN
    Charaktere c1 ON(c1.Klasse = k.Schwächen)
GROUP BY c.Name, (k.Basisleben * c.Leben_Multiplikator)
HAVING (k.Basisleben * c.Leben_Multiplikator) - Sum(c1.Schaden) > 0
--
--  R e s u l t :
--
--  Name    Zustand
--  ------- -------
--  Sylvar     1085
--  Vega       1085
--  Falk        885
--  Venthyr     197
--  Matep        87

С уважением...

Спасибо за ваш ответ, мне пришлось выбрать одно из другого, и я выбрал первого, кто ответил (и соответственно обновил). Но мне, тем не менее, нравится ваш ответ, поскольку он также помогает мне получить более глубокое представление о том, как избежать подзапросов, спасибо!

Philip Gertsch 21.11.2022 21:09

Другие вопросы по теме