Question

http://www.db-fiddle.com/f/5oPihy8pysUfUGMPA9GNMC/0"rel=“nofollow noreferer”>SQL Fiddle。

这些是投入表:

学生表:

student_id	student_name
1	Daniel
2	Jade
3	Stella
4	Jonathan
5	Will

表格:

exam_id	student_id	score
10	1	70
10	2	80
10	3	90
20	1	80
30	1	70
30	3	80
30	4	90
40	1	60
40	2	70
40	4	80

我需要找到“静坐”学生。普通学生至少参加一次考试,未得分最高或最低。我需要撰写一份解决办法,报告在<><<>all>/strong>考试中度过的学生(学生姓名)。从未参加过考试的学生不得参加考试。

就此而言,这是一个先谈问题,我已经通过其他手段解决了这一问题。我特别想理解以下提问为什么不可行。

with student_scores as (
select student_id, student_name, 
rank() over (partition by exam_id order by score asc) worse_rank,
rank() over (partition by exam_id order by score desc) best_rank

from Student
inner join Exam using (student_id)
)

select distinct student_id, student_name
from student_scores
where student_id not in (

select student_id
from student_scores
where ((worse_rank=1) or (best_rank=1)) and (student_id is not null)
)

However, this returns no record whereas I am expecting the record for student_id 2, Jade.

我在总结时发现的一些情况:

It works if I use another CTE like so:

loud as (
  select distinct student_id
  from student_scores
  where ((worse_rank=1) or (best_rank=1)) and (student_id is not null)
)

之后使用

where student_id not in (select student_id from loud)

但是,如果我用别的在外语中的话,这只会奏效!

It works if I create the input tables by explicitly defining student_id as the PRIMARY KEY.
It also works if I use student_name in the where not in clause instead of student_id.
Adding distinct to the subquery after the where not in clause does not help.

Answer 1

I would use an aggregation approach here, with the help of the RANK() window function:

WITH cte AS (
    SELECT *, RANK() OVER (PARTITION BY exam_id ORDER BY score) rnk1,
              RANK() OVER (PARTITION BY exam_id ORDER BY score DESC) rnk2
    FROM Exam
)

SELECT s.student_id, s.student_name
FROM Student s
INNER JOIN cte t
    ON t.student_id = s.student_id
GROUP BY s.student_id, s.student_name
HAVING SUM(t.rnk1 = 1) = 0 AND SUM(t.rnk2 = 1) = 0;

《条款》规定,任何对等学生在任何考试中都不是最低或最高评分。

友情链接