SQL 显示 MIN() 列，并显示其他列

Question

我正在处理一份报告，我需要在其中显示学生证、分数和提交分数的最低日期/时间。该表将为每个学生在多天内提供多个分数。

我遇到的问题(使用下面的示例代码(是我为每个分数获得一个新行，而不是每个学生只有一行显示学生 ID、分数和日期。

示例源表列

student_id | 得分 | score_date

select tests.student_id
,tests.score
,min(score_date)
from tests
group by tests.student_id
,tests.score

Answer 1

你用row_number()来表示这个。

select * from (
select t1.student_id
,t1.score
,row_number() over (partition by t1.student_id order by t1.score_date) rn 
,t1.score_date
from tests t1) t
where t.rn = 1

还有一个技巧 使用correlated subquery.

select student_id
, min(score_date) score_date
, (select top 1 score from tests where score_date = min(t.score_date) and student_id = t.student_id) as score
from tests t
group by student_id

或者，如果您使用的是SQL Server 2012及更高版本。您可以最大限度地利用first_value()功能。

select distinct first_value(student_id) over (partition by student_id order by score_date)
, first_value(score_date) over (partition by student_id order by score_date)
, first_value(score) over (partition by student_id order by score_date)
from tests t
group by student_id

Answer 2

试试这个。

SELECT student_id, score, score_date
FROM tests T 
INNER JOIN (SELECT student_id, Min(score_date) AS score_date FROM tests) X 
ON T.student_id = X.student_id AND T.score_date = X.score_date

Answer 3

如果您想要每个学生得分最低时的所有日期？

然后想到DENSE_RANK窗口功能。 按升序排序score.

WITH CTE_STUDENT_SCORES AS
(
SELECT student_id, score, score_date
, DENSE_RANK() OVER (PARTITION BY student_id ORDER BY score ASC) AS StudentAscendingScoreRank
FROM tests
)
SELECT student_id, score, score_date
FROM CTE_STUDENT_SCORES
WHERE StudentAscendingScoreRank = 1
ORDER BY student_id, score_date

如果只需要学生分数最低的最新score_date？
然后可以限制ROW_NUMBER。
按分数升序和score_date降序排序。

WITH CTE_STUDENT_SCORES AS
(
SELECT student_id, score, score_date
, ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY score ASC, score_date DESC) AS StudentAscendingScoreRownum
FROM tests
)
SELECT student_id, score, score_date
FROM CTE_STUDENT_SCORES
WHERE StudentAscendingScoreRownum = 1
ORDER BY score_date DESC, student_id;