sql - 返回查询中比较的结果

Question

我正在处理下表之间的查询：

任务：

    id | name
  -----+-------------
   101 | MinDist
   123 | Equi
   142 | Median
   300 | Tricoloring

报告：

   id | task_id  | candidate         | score
  ----+----------+-------------------+--------
   13 | 101      | John Smith        | 100
   24 | 123      | Delaney Lloyd     | 34
   37 | 300      | Monroe Jimenez    | 50
   49 | 101      | Stanley Price     | 45
   51 | 142      | Tanner Sears      | 37
   68 | 142      | Lara Fraser       | 3
   83 | 300      | Tanner Sears      | 0

我的输出应该是这样的：

    task_id | task_name    | difficulty
   ---------+--------------+------------
        101 | MinDist      | Easy
        123 | Equi         | Medium
        142 | Median       | Hard
        300 | Tricoloring  | Medium

到目前为止，我所做的就是这样，但是不起作用：

SELECT rp.task_id, tasks.name, difficulty 
FROM tasks 
JOIN reports AS rp
WHERE   
    case when AVG(rp.score) < 20 then difficulty 'Hard' end as difficulty,
    case when 20 < AVG(rp.score) < 60 then difficulty 'Medium' end as difficulty,
    case when 60 < AVG(rp.score)  then difficulty 'Easy' end as difficulty,
GROUPBY rp.task_id;

我得到这个错误：

输出（stderr）：“WHERE”处或附近的语法错误第 5 行：WHERE
^

标签： sql

Answer 1

您需要在以下定义列SELECT：

SELECT t.id, t.name, 
       (case when AVG(rp.score) < 20 then 'Hard'
             when AVG(rp.score) < 60 then 'Medium' 
             else 'Easy'
        end) as difficulty
FROM tasks t JOIN
     reports rp
     ON t.id = rp.task_id
GROUP BY t.id, t.name;