gpt4 book ai didi

sql - TSQL : Max date but include other variables

转载 作者:行者123 更新时间:2023-12-02 05:29:20 25 4
gpt4 key购买 nike

我正在尝试查找具有最大日期的记录,这很简单,但是当我想要其他属性时它不起作用。

我有 3 张 table 。

表 1 = 学生。

Columns STid, STname
Value 1, John
2, Bob Lee
3, Jane

表 2 = 测试。

Columns Testid, STid, Date, Grade
Value 1, 1, 20/05/2018, A
2, 2, 20/05/2018, B
3, 3, 20/05/2018, D
4, 1, 30/06/2018, C
5, 2, 30/06/2018, C
6, 3, 30/06/2018, B

我想要的是每个学生上次测试的成绩。

输出应该是..

Columns STname, Date, Grade
Value John, 1, 30/06/2018, C
Bob Lee, 2, 30/06/2018, C
Jane, 3, 30/06/2018, B

当我尝试以下操作时,它工作正常,但我需要成绩。

SELECT STNAME, MAX(T.DATE)
FROM STUDENT S, TEST T
WHERE S.STID = T.STID
GROUP BY STNAME

不幸的是,下面的方法不起作用。

SELECT STNAME, MAX(T.DATE), T.GRADE
FROM STUDENT S, TEST T
WHERE S.STID = T.STID
GROUP BY STNAME

或者下面只是带回所有内容。

SELECT STNAME, MAX(T.DATE), T.GRADE
FROM STUDENT S, TEST T
WHERE S.STID = T.STID
GROUP BY STNAME, T.GRADE.

我怀疑答案在于子查询,但我也无法让它工作。

SELECT STNAME, T.DATE, T.GRADE
FROM STUDENT S, TEST T
WHERE S.STID = T.STID
AND T.DATE = (SELECT MAX(T.DATE)
FROM STUDENT S, TEST T
WHERE S.STID = T.STID )

OR

SELECT STNAME, T.DATE, T.GRADE
FROM STUDENT S, TEST T
WHERE S.STID = T.STID
AND T.DATE = (SELECT MAX(T2.DATE)
FROM STUDENT S2, TEST T2
WHERE S.STID = S2.STID )

感谢任何帮助。

最佳答案

您需要row_number()函数:

select t.*
from (select s.stid, s.STname, t.Date, t.Grade,
row_number() over (partition by s.stid order by t.date desc) as seq
from student s inner join
test t
on t.stid = s.stid
) t
where seq = 1;

关于sql - TSQL : Max date but include other variables,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51241807/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com