SQL：不使用group-by的GROUPBY陳述句

問題：如何在不使用 GROUP BY 的情況下為以下查詢獲得相同的結果？

有人可以為我提供一個不使用 GROUP BY的解決方案嗎？

SELECT Klasse, COUNT( ) AS Anzahl FROM Charaktere WHERE Schaden > 700 GROUP BY Klasse HAVING COUNT( ) > 1

該陳述句必須在 oracle 服務器上運行。

測驗資料：

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

請不要告訴我為什么等等......我只需要回答我的問題:-)

uj5u.com熱心網友回復：

好的，一種方法是使用COUNTwithOVER子句，并DISTINCT得到不同的結果：

SELECT DISTINCT Klasse, Anzahl
FROM (
       SELECT Klasse, COUNT(*) OVER(PARTITION BY Klasse) AS Anzahl
       FROM Charaktere
       WHERE Schaden > 700
     ) 
WHERE Anzahl > 1;

結果是：

uj5u.com熱心網友回復：

假設有一個主鍵約束Klassen.Klasse和一個外鍵約束Charaktere.Klasse（或者如果約束實際上不存在，因為您似乎對樣本資料沒有約束，則有一個預期的約束），那么您可以使用：

SELECT *
FROM   (
  SELECT Klasse,
         ( SELECT COUNT(*)
           FROM   Charaktere c
           WHERE  Schaden > 700
           AND    c.Klasse = K.Klasse ) AS anzhal
  FROM   Klassen k
)
WHERE  anzhal > 1;

或者：

SELECT k.klasse,
       c.anzhal
FROM   Klassen k
       CROSS JOIN LATERAL (
         SELECT COUNT(*) AS anzhal
         FROM   Charaktere c
         WHERE  Schaden > 700
         AND    c.Klasse = K.Klasse
       ) c
WHERE  c.anzhal > 1;

其中，對于您的樣本資料，兩者都輸出：

小提琴

uj5u.com熱心網友回復：

如果您不想（出于任何原因）使用清晰易讀的group by，那么您可以：

• 隱式使用pivot該組

with tab as (
  select
    Klasse,
    'A' as col
  from Charaktere 
  where schaden > 700
)
select *
from tab
pivot (
  count(1)
  for col in ('A' as anzahl)
)
where anzahl > 1

• 或者通過match_recognize的聚合能力進行計算

select
  Klasse,
  cnt AS Anzahl 
from Charaktere 
match_recognize (
  partition by klasse
  order by schaden
  measures
    count(1) as cnt
  pattern (a a )
  /*More than one consecutive klasse
  with schaden > 700*/
  define
    a as schaden > 700
)
order by 1

• 或使用橫向連接

select distinct
  c.klasse,
  l.anzahl
from Charaktere c
  cross join lateral (
    select
      count(1) as anzahl
    from Charaktere c2
    where c2.schaden > 700
    and c2.klasse = c.klasse
  ) l
where l.anzahl > 1
order by 1

• 甚至在 XQuery 和序列化的幫助下XMLType

select *
from xmltable(
'(: Grouping by distinct :)
for $x in distinct-values($rs/ROWSET/ROW/KLASSE)
let $cnt := count($rs/ROWSET/ROW[KLASSE=$x])
where $cnt > 1
return <row klasse="{$x}" anzahl="{$cnt}"/>'
  passing dbms_xmlgen.getxmltype(
'select
  Klasse
from Charaktere 
where schaden > 700') as "rs"

columns
  klasse varchar2(100) path '@klasse',
  anzahl int path '@anzahl'
)
order by 1

以上所有內容都會為您的示例資料回傳此結果：

小提琴

標籤：数据库甲骨文

上一篇：如果sql中的值是正數，如果是負數怎么操作呢？

下一篇：帶有比較的SQLwhere子句