请教一条 sql 的写法

[uuweZhou]

### 以下数据结构，你如何查询某一日期或之前，学生在某年级的单科最新成绩分数，按学生姓名进行排正序取 10 条，请写 SQL 。在特定日期、每个学生在同一年级、同一学科只返回一条成绩数据。查询时，如果特定日期没有成绩数据，则需要返回这个日期之前的最新（离特定日期最近）成绩数据。


| 科目 | 年级 | 学生 | 成绩日期 | 成绩分数 |
| ---- | ---- | ---- | ---------- | ---- |
| 语文 | 1 | 张三 | 2015-01-03 | 100 |
| 语文 | 1 | 李四 | 2015-01-03 | 80 |
| 语文 | 1 | 张三 | 2015-01-05 | 80 |
| 语文 | 1 | 李四 | 2015-01-05 | 90 |
| 数学 | 1 | 张三 | 2015-01-04 | 80 |

提醒：如果要取 2015-01-05 的数据，要求得到张三的语文 1 年级成绩为 80 分，李四的语文 1 年级成绩为 90 分，张三的数学成绩为 80 分。要考虑到在同一天、同一学科、同一年级不是所有学生都有成绩。执行 SQL 查询需要得到的结果如下：


| 科目 | 年级 | 学生 | 成绩日期 | 成绩分数 |
| ---- | ---- | ---- | ---------- | ---- |
| 语文 | 1 | 李四 | 2015-01-05 | 90 |
| 数学 | 1 | 张三 | 2015-01-05 | 80 |


我的思路是:case when + group by

求解~

[uuweZhou]

@dong3580

[uuweZhou]

@nullp

[uuweZhou]

@luckylion

[zeraba]

描述的有点绕 就是求 科目 | 年级 | 学生 的最新日期下面的分数呗 select 1 2 3 max(4) 5 from list 不是就好了么

[uxstone]

试试逻辑判断部分用代码去做
没必要用一条 sql 就搞定所有

[uuweZhou]

@zeraba 显示不是的.日期是变量.

[zeraba]

另外按照描述
| 语文 | 1 | 张三 | 2015-01-05 | 80 |
这条数据应该也是在结果页的 张三的语文成绩也是复合需求的

[zeraba]

@uuweZhou 日期用其它前端语言传参啊 如果是要在某个日期之前 就加个 where 如果没有参数 就传当前日期

[billlee]

SELECT * FROM t1 NATURAL JOIN (SELECT `学生`, `科目`, `年级`, MAX(`成绩日期`) AS `成绩日期` FROM t1 WHERE `成绩日期` < "2015-01-05" GROUP BY `学生`, `科目`, `年级` ORDER BY `学生` LIMIT 10)

大概这样？如果不能 JOIN 换成 WHERE IN 试试

[zeraba]

@billlee 他的 limit 10 估计是单姓名 就是取 10 个人不同科目的最新成绩 一个方案是 1 2 笛卡尔积 乘以 10 作为 limit 后面的值 还可以把姓名排序后的结果前 10 做为一个子查询去 left join 这个更新维护比较容易理解

[ivvei]

先说数据库。不同的数据库支持的 SQL 都不一样。

[ivvei]

如果是 Oracle 的话用分析函数秒解。

select distinct 科目，年级，学生，'2015-01-05' as 成绩日期, first_value(成绩分数)over(partition by 科目，年级，学生 order by 成绩日期 desc) as 成绩分数
from 表
where 成绩日期 <= '2015-01-05'
order by 学生

然后外面套一层取个 10 条。

当然你给的示例是错的。张三的语文成绩呢？

[mortonnex]

@ivvei @zeraba @billlee
"如果特定日期没有成绩数据，则需要返回这个日期之前的最新（离特定日期最近）成绩数据"

[CRVV]

和下面这道题几乎一样，用 MySQL 似乎没有好的解法

https://leetcode.com/problems/department-top-three-salaries

在正经的关系型数据库上用 Window function 或者 LATERAL JOIN 都可以搞定，比如上面 ivvei 的解法。

[cchilar]

@mortonnex

@ivvei 就是能实现的。

[widewing]

Window function 没有的话 group_concat

[ayumilove]

题我没太看明白（ 10 条哪里，如果是针对个人的最新 10 条成绩，就再加一层嵌套。），
以下只包含了取特定日期最新成绩的功能。
用基本的 exists 就能实现吧。

SELECT a.*
FROM 成绩表 a
where exists (select a.科目
from (SELECT
科目,年级,学生,MAX(成绩日期) 成绩日期
FROM 成绩表
WHERE 成绩日期 <= '2015-01-05'
GROUP BY 科目,年级,学生) b
WHERE a.科目 = b.科目
and a.年级 = b.年级
and a.学生 = b.学生
and a.成绩日期 = b.成绩日期);

[shakoon]

select * from table
where (科目,年级,学生,成绩日期) =
(select 科目,年级,学生,max(成绩日期) from table
where 成绩日期 <= 查询的日期 group by 科目,年级,学生);
--oracle 语法

[ivvei]

@shakoon in

[nullp]

查询时，如果特定日期没有成绩数据，则需要返回这个日期之前的最新（离特定日期最近）成绩数据。

要考虑到在同一天、同一学科、同一年级不是所有学生都有成绩

这两条是不是有矛盾，按照第一条要求，就是要显示所有学科，所有学生的成绩
第二条的意思有好像是说没有就不显示？

[luckylion]

select * from
(select * from `成绩` where `成绩日期` <= '2015-01-05' order by `成绩日期` desc)
as a GROUP BY 学生,科目,年级
ORDER BY `学生` limit 10

| 科目 | 年级 | 学生 | 成绩日期 | 成绩分数 |
| ---- | ---- | ---- | ---------- | ---- |
| 数学 | 1 | 张三 | 2015-01-04 | 80 |
| 语文 | 1 | 张三 | 2015-01-05 | 80 |
| 语文 | 1 | 李四 | 2015-01-05 | 90 |


--------------------------------------------------------------------------------------------------------
如果是筛选 10 位同学,外层还需再套一层 最好通过生成临表再进行筛选减少运算次数