I have 2 questions concerning re-writting queries, so that no subquery is needed.
- Is it - using Oracle-SQL-statements - possible to write one of the following queries without using any type of subquery (e.g. subselect)?
- If yes, how would this be possible.
Query #1 is as follows:
SELECT Stufe, (SELECT AVG(Stufe) FROM Charaktere) FROM Charaktere
WHERE Stufe > (SELECT AVG(Stufe) FROM Charaktere) ORDER BY Stufe DESC
Query #2 is as follows:
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
Test data:
`
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');
`
I already searched on StackOverflow but cannot re-write these two queries based on the provided information. I guess, the use of JOINS and/or GROUP BY would suffice, ...?
CodePudding user response：
You can use a CTE to make it bit more simple.
The first query can be rewritten as :
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
And the second query can be rewritten as
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
Without using CTE :
The above 2 queries can be rearranged to avoid the CTE
query 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
query 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
if you take a closer look, you can see the outer tables from the earlier queries are a joining table now.
CodePudding user response：
Well, CTE is a kind of subquery quote from docs
Common Table Expressions -
To specify common table expressions, use a WITH clause that has one or more comma-separated subclauses. Each subclause provides a subquery that produces a result set, and associates a name with the subquery.
Your Query 1 could be coded without subquery like here:
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
... not sure about the Query 2 but will try to figure it out. Maybe...
Query 2 without subqueries:
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
Regards...