V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
uuweZhou
V2EX  ›  程序员

请教一条 sql 的写法

  •  
  •   uuweZhou · 2017-04-05 20:47:52 +08:00 · 2876 次点击
    这是一个创建于 2788 天前的主题,其中的信息可能已经有所发展或是发生改变。
    ### 以下数据结构,你如何查询某一日期或之前,学生在某年级的单科最新成绩分数,按学生姓名进行排正序取 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

    求解~
    21 条回复    2017-04-07 14:32:23 +08:00
    uuweZhou
        1
    uuweZhou  
    OP
       2017-04-05 20:53:10 +08:00
    uuweZhou
        2
    uuweZhou  
    OP
       2017-04-05 20:53:36 +08:00
    uuweZhou
        3
    uuweZhou  
    OP
       2017-04-05 20:54:43 +08:00
    zeraba
        4
    zeraba  
       2017-04-05 21:06:55 +08:00 via Android
    描述的有点绕 就是求 科目 | 年级 | 学生 的最新日期下面的分数呗 select 1 2 3 max(4) 5 from list 不是就好了么
    uxstone
        5
    uxstone  
       2017-04-05 21:10:12 +08:00
    试试逻辑判断部分用代码去做
    没必要用一条 sql 就搞定所有
    uuweZhou
        6
    uuweZhou  
    OP
       2017-04-05 21:10:24 +08:00
    @zeraba 显示不是的.日期是变量.
    zeraba
        7
    zeraba  
       2017-04-05 21:10:31 +08:00 via Android
    另外按照描述
    | 语文 | 1 | 张三 | 2015-01-05 | 80 |
    这条数据应该也是在结果页的 张三的语文成绩也是复合需求的
    zeraba
        8
    zeraba  
       2017-04-05 21:13:15 +08:00 via Android
    @uuweZhou 日期用其它前端语言传参啊 如果是要在某个日期之前 就加个 where 如果没有参数 就传当前日期
    billlee
        9
    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
        10
    zeraba  
       2017-04-05 21:55:35 +08:00 via Android
    @billlee 他的 limit 10 估计是单姓名 就是取 10 个人不同科目的最新成绩 一个方案是 1 2 笛卡尔积 乘以 10 作为 limit 后面的值 还可以把姓名排序后的结果前 10 做为一个子查询去 left join 这个更新维护比较容易理解
    ivvei
        11
    ivvei  
       2017-04-05 22:55:17 +08:00
    先说数据库。不同的数据库支持的 SQL 都不一样。
    ivvei
        12
    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
        13
    mortonnex  
       2017-04-05 23:20:51 +08:00
    @ivvei @zeraba @billlee
    "如果特定日期没有成绩数据,则需要返回这个日期之前的最新(离特定日期最近)成绩数据"
    CRVV
        14
    CRVV  
       2017-04-06 01:30:21 +08:00
    和下面这道题几乎一样,用 MySQL 似乎没有好的解法

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

    在正经的关系型数据库上用 Window function 或者 LATERAL JOIN 都可以搞定,比如上面 ivvei 的解法。
    cchilar
        15
    cchilar  
       2017-04-06 07:38:22 +08:00 via Android
    @mortonnex

    @ivvei 就是能实现的。
    widewing
        16
    widewing  
       2017-04-06 08:49:27 +08:00 via Android
    Window function 没有的话 group_concat
    ayumilove
        17
    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
        18
    shakoon  
       2017-04-06 09:19:03 +08:00
    select * from table
    where (科目,年级,学生,成绩日期) =
    (select 科目,年级,学生,max(成绩日期) from table
    where 成绩日期 <= 查询的日期 group by 科目,年级,学生);
    --oracle 语法
    ivvei
        19
    ivvei  
       2017-04-06 10:16:35 +08:00
    nullp
        20
    nullp  
       2017-04-06 14:05:32 +08:00
    查询时,如果特定日期没有成绩数据,则需要返回这个日期之前的最新(离特定日期最近)成绩数据。

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

    这两条是不是有矛盾,按照第一条要求,就是要显示所有学科,所有学生的成绩
    第二条的意思有好像是说没有就不显示?
    luckylion
        21
    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 位同学,外层还需再套一层 最好通过生成临表再进行筛选减少运算次数
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2673 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 12:38 · PVG 20:38 · LAX 04:38 · JFK 07:38
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.