请教一条 sql 的写法

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 2787 天前的主题，其中的信息可能已经有所发展或是发生改变。

### 以下数据结构，你如何查询某一日期或之前，学生在某年级的单科最新成绩分数，按学生姓名进行排正序取 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

求解~

2015-01-05

语文

年级

成绩

21 条回复 • 2017-04-07 14:32:23 +08:00

uuweZhou

2017-04-05 20:53:10 +08:00

@dong3580

uuweZhou

2017-04-05 20:53:36 +08:00

@nullp

uuweZhou

2017-04-05 20:54:43 +08:00

@luckylion

zeraba

2017-04-05 21:06:55 +08:00 via Android

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

uxstone

2017-04-05 21:10:12 +08:00

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

uuweZhou

2017-04-05 21:10:24 +08:00

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

zeraba

2017-04-05 21:10:31 +08:00 via Android

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

zeraba

2017-04-05 21:13:15 +08:00 via Android

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

billlee

2017-04-05 21:31:03 +08:00

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

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

zeraba

2017-04-05 21:55:35 +08:00 via Android

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

ivvei

2017-04-05 22:55:17 +08:00

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

ivvei

2017-04-05 23:05:50 +08:00

如果是 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

2017-04-05 23:20:51 +08:00

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

CRVV

2017-04-06 01:30:21 +08:00

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

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

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

cchilar

2017-04-06 07:38:22 +08:00 via Android

@mortonnex

@ivvei 就是能实现的。

widewing

2017-04-06 08:49:27 +08:00 via Android

Window function 没有的话 group_concat

ayumilove

2017-04-06 09:16:56 +08:00

题我没太看明白（ 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

2017-04-06 09:19:03 +08:00

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

ivvei

2017-04-06 10:16:35 +08:00

@shakoon in

nullp

2017-04-06 14:05:32 +08:00

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

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

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

luckylion

2017-04-07 14:32:23 +08:00

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 位同学,外层还需再套一层最好通过生成临表再进行筛选减少运算次数