排除SQL中小于最大值的dup

Question

我有下面的简单表(Table1)，其中每一行是一个student_ID和他们的名字，每个学生有一个或多个wins (wins)。我想输出:Student_ID, Student_name，获胜次数，按获胜次数(降序)排序，然后按Student_ID(升序)排序，不包括那些拥有相同的获胜次数的学生，其次数小于获胜的最大值(即5)。换句话说，Lizzy和Mark的获胜次数相同，并且3比5少，因此输出将排除两个学生Lizzy和Mark。

From comments:"Betty, David和Cathy也应该被排除在外">

Table1:

<表类> student_id student_name 赢得 tbody><<tr>1约翰对1约翰对1约翰对1约翰对1约翰对2布兰登对2布兰登对2布兰登对2布兰登对2布兰登对3丽萃对3丽萃对3丽萃对4标记对4标记对4标记对5贝蒂对6大卫对7凯茜对8乔对8乔对

在'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 ...

根据更新的需求更新…

这个要求是模糊的，因为贝蒂、大卫和凯茜似乎也符合从结果中删除的标准。这个要求已经澄清，这些行应该被删除。

逻辑已添加到只允许所有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)):

tbody> <<tr>28

STUDENT_ID	STUDENT_NAME	CNT_WINS
1	约翰	5
	布兰登	5
	乔	2