仅分配与条件相关的条目

Question

我有以下代码，

with my_table (id, student, category, score)
as (values 
(1, 'Alex', 'A', 11),
(2, 'Alex', 'D', 4),
(3, 'Bill', 'A', 81),
(4, 'Bill', 'B', 1),
(5, 'Bill', 'D', 22),
(6, 'Carl', 'C', 5),
(7, 'Carl', 'D', 10)
)

select
  id, student, category, score,
  case when 
         max(score) filter (where category in ('A', 'B', 'C')) over (partition by student) >
         min(score) filter (where category = 'D') over (partition by student)
      then 'Review'
      else 'Pass'
  end as result
from my_table
order by student, id

该代码可以输出

id  student   category    score   conclusion
1    Alex     A           11       Review
2    Alex     D           4        Review
3    Bill     A           81       Review
4    Bill     B           1        Review
5    Bill     D           22       Review
6    Carl     C           5        Pass
7    Carl     D           10       Pass

如何编辑它，因此仅将A，B或C大于D的条目分配给它们。因此，在这种情况下，所需的输出将是：

id  student   category    score   conclusion
1    Alex     A           11       Review
2    Alex     D           4        Review
3    Bill     A           81       Review
4    Bill     B           1        Pass
5    Bill     D           22       Review
6    Carl     C           5        Pass
7    Carl     D           10       Pass

对于比尔，a＆gt; d因此将其分配给它； b＆lt; d SO分配给它。

Answer 1

从您的逻辑中，您可以尝试使用子查询获取count然后比较

with my_table (id, student, category, score)
as (values 
(1, 'Alex', 'A', 11),
(2, 'Alex', 'D', 4),
(3, 'Bill', 'A', 81),
(4, 'Bill', 'B', 1),
(5, 'Bill', 'D', 22),
(6, 'Carl', 'C', 5),
(7, 'Carl', 'D', 10)
)
SELECT  id, student, category, score,
       CASE WHEN 
        COUNT(*) filter (where D_Score<score) over (partition by student) = 0 OR score < D_Score 
        THEN 'Pass'
        ELSE 'Review' END
FROM (
    SELECT *,
       min(score) filter (where category = 'D') over (partition by student) D_Score
    FROM my_table
) t1

sqlfiddle