时间戳

首页 > 解决方案 > 从与另一列的最大值关联的列中返回一个值

sql - 从与另一列的最大值关联的列中返回一个值

问题描述

抱歉标题令人困惑，但我不知道如何表达我的问题。

我有一个查询，它返回一个测试分数表，以及测试本身的一些描述：

+----------------+------------+-----------+ | student_id | test_score | test_term | +----------------+------------+-----------+ | 1 123 | 614 | Spring | | 2 123 | 547 | Summer | | 3 123 | 628 | Fall | +----------------+------------+-----------+

如您所见，学生 123 参加了 3 次数学考试。我正在尝试编写一个查询，该查询将返回学生 123 取得的最高分数，以及与该分数相关的测试术语。这是我到目前为止所拥有的：

SELECT
   MAX (test_score) as "highest_math_score",
   CASE WHEN test_score = MAX(test_score) THEN test_term 
        ELSE null 
   END as "highest_test_term"
FROM Table1
GROUP BY
   student_id

但是，我收到错误：not a GROUP BY expression.

关于如何做到这一点的任何想法？

标签： sqloracleplsql

解决方案

您可以使用MAX..KEEP：

SELECT student_id, 
       max(test_score), 
       max(test_term) KEEP ( DENSE_RANK FIRST ORDER BY test_score desc )
FROM test_data
GROUP BY student_id;

完整示例：

WITH test_data ( student_id, test_score, test_term ) AS
( SELECT 123, 614, 'Spring' FROM DUAL UNION ALL
 SELECT 123, 547, 'Summer' FROM DUAL UNION ALL
 SELECT 123, 628, 'Fall' FROM DUAL UNION ALL
 SELECT 456, 999, 'Spring' FROM DUAL UNION ALL
 SELECT 456, 1111, 'Summer' FROM DUAL UNION ALL
 SELECT 456, 888, 'Fall' FROM DUAL )
SELECT student_id, max(test_score), max(test_term) KEEP ( DENSE_RANK FIRST ORDER BY test_score desc )
FROM test_data
GROUP BY student_id;

+------------+-----------------+--------+
| STUDENT_ID | MAX(TEST_SCORE) |  TERM  |
+------------+-----------------+--------+
|        123 |             628 | Fall   |
|        456 |            1111 | Summer |
+------------+-----------------+--------+

推荐阅读