排除SQL中低于最大值的dup

我有以下簡單表（Table1），其中每一行是一個 student_ID 和他們的名字，每個學生都有一個或多個勝利（Wins）。我想輸出：Student_ID，Student_name，Wins 計數，按 Wins 計數（降序）然后 Student_ID（升序）排序，不包括那些 Wins 計數小于 Wins 最大值的學生（即 5 ）。換句話說，Lizzy 和 Mark 的獲勝次數相同，并且 3 小于 5，因此輸出將排除 Lizzy 和 Mark 兩個學生。

來自評論：“Betty、David 和 Cathy 也應該被排除在外”。

表格1：

期望的輸出：

這是我在 Oracle 中的 SQL。我無法弄清楚出了什么問題。日志顯示“(SELECT b.cnt_wins, count(b.student_id) 的值太多”。

WITH st_cte AS
(SELECT student_id, student_name, count(wins) cnt_wins
FROM Table1
GROUP BY student_id, student_name
ORDER BY count(wins) DESC, student_id)
SELECT *
FROM st_cte a
WHERE a.cnt_wins not in
(SELECT b.cnt_wins, count(b.student_id)
 FROM st_cte b
 WHERE b.cnt_wins <
 (SELECT max(c.cnt_wins) FROM st_cte c)
 GROUP BY b.cnt_wins
 HAVING count(b.student_id) > 1);

uj5u.com熱心網友回復：

在“in”選擇中選擇了太多值：

WHERE a.cnt_wins -- 1 value
  not in
       (SELECT b.cnt_wins, count(b.student_id) -- 2 values
         FROM st_cte b

你應該這樣做：

WHERE a.cnt_wins not in
       (SELECT b.cnt_wins
         FROM st_cte ...

或者

 WHERE (a.cnt_wins, count(something)) not in
        (SELECT b.cnt_wins, count(b.student_id)
          FROM st_cte ...

uj5u.com熱心網友回復：

根據更新的要求更新...

該要求是模棱兩可的，因為 Betty、David 和 Cathy 似乎也符合從結果中洗掉的標準。此要求已澄清，應洗掉這些行。

添加了邏輯以僅允許所有max_cnt行，以及具有唯一計數的任何學生。

還要注意，如果wins可以是任何其他非空值，COUNT(wins)是不正確的。

鑒于所有這些，也許這樣的事情是一個起點：

小提琴

WITH cte AS (
        SELECT student_id, student_name
             , COUNT(wins) cnt_wins
             , MAX(COUNT(wins)) OVER () AS max_cnt
          FROM Table1
         GROUP BY student_id, student_name
     )
   , cte2 AS (
        SELECT cte.*
             , COUNT(*) OVER (PARTITION BY cnt_wins) AS cnt_students
          FROM cte
     )
SELECT student_id, student_name, cnt_wins
  FROM cte2
 WHERE max_cnt = cnt_wins
    OR cnt_students = 1
 ORDER BY cnt_wins DESC, student_id
;

并處理wins可以是其他非空值：

WITH cte AS (
        SELECT student_id, student_name
             , COUNT(CASE WHEN wins = 'YES' THEN 1 END) cnt_wins
             , MAX(COUNT(CASE WHEN wins = 'YES' THEN 1 END)) OVER () AS max_cnt
          FROM Table1
         GROUP BY student_id, student_name
     )
   , cte2 AS (
        SELECT cte.*
             , COUNT(*) OVER (PARTITION BY cnt_wins) AS cnt_students
          FROM cte
     )
SELECT student_id, student_name, cnt_wins
  FROM cte2
 WHERE max_cnt = cnt_wins
    OR cnt_students = 1
 ORDER BY cnt_wins DESC, student_id
;

結果（帶有測驗新要求的資料，一名學生 (Joe) 具有唯一計數 (2)）：

設定：

CREATE TABLE table1 (
    Student_ID   int
  , Student_Name VARCHAR2(20)
  , Wins         VARCHAR2(10)
);


BEGIN
-- Assume only wins are stored.

INSERT INTO table1 VALUES ( 1, 'John', 'YES');
INSERT INTO table1 VALUES ( 1, 'John', 'YES');
INSERT INTO table1 VALUES ( 1, 'John', 'YES');
INSERT INTO table1 VALUES ( 1, 'John', 'YES');
INSERT INTO table1 VALUES ( 1, 'John', 'YES');
INSERT INTO table1 VALUES ( 2, 'Brandon', 'YES');
INSERT INTO table1 VALUES ( 2, 'Brandon', 'YES');
INSERT INTO table1 VALUES ( 2, 'Brandon', 'YES');
INSERT INTO table1 VALUES ( 2, 'Brandon', 'YES');
INSERT INTO table1 VALUES ( 2, 'Brandon', 'YES');

INSERT INTO table1 VALUES ( 3, 'Lizzy', 'YES');
INSERT INTO table1 VALUES ( 3, 'Lizzy', 'YES');
INSERT INTO table1 VALUES ( 3, 'Lizzy', 'YES');

INSERT INTO table1 VALUES ( 4, 'Mark', 'YES');
INSERT INTO table1 VALUES ( 4, 'Mark', 'YES');
INSERT INTO table1 VALUES ( 4, 'Mark', 'YES');

INSERT INTO table1 VALUES ( 5, 'Betty', 'YES');

INSERT INTO table1 VALUES ( 6, 'David', 'YES');
INSERT INTO table1 VALUES ( 7, 'Cathy', 'YES');

INSERT INTO table1 VALUES ( 8, 'Joe', 'YES');
INSERT INTO table1 VALUES ( 8, 'Joe', 'YES');
END;
/

更正問題中的原始查詢：

WITH st_cte AS
  (SELECT student_id, student_name, count(wins) cnt_wins
     FROM Table1
    GROUP BY student_id, student_name
    ORDER BY count(wins) DESC, student_id
  )
SELECT *
  FROM st_cte a
 WHERE a.cnt_wins not in
   (SELECT b.cnt_wins
      FROM st_cte b
     WHERE b.cnt_wins < (SELECT max(c.cnt_wins) FROM st_cte c)
     GROUP BY b.cnt_wins
    HAVING count(b.student_id) > 1
   )
;

標籤：sql 甲骨文

上一篇：創建模式之間的鏈接時的權限問題

下一篇：OracleSQL以重復模式更新行