V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
UN2758
V2EX  ›  问与答

SQL 不会写了,求问一个简单 SQL 语句

  •  
  •   UN2758 · 2022-08-02 21:50:10 +08:00 · 1293 次点击
    这是一个创建于 835 天前的主题,其中的信息可能已经有所发展或是发生改变。

    Q:已知 class_id stu_id course_id 在表 t 中,求每个班级中被选次数最多的课程 id ,返回字段 class_id course_id

    7 条回复    2022-08-05 04:24:03 +08:00
    sutra
        1
    sutra  
       2022-08-02 21:57:52 +08:00
    select class_id, course_id, count(*) c from t group by class_id, course_id order by c desc;
    reter
        2
    reter  
       2022-08-02 22:38:53 +08:00   ❤️ 1
    select
    class_id,
    course_id
    from (
    -- 根据每个班级下的选课人数, 给课程确定顺序
    select
    class_id,
    course_id,
    stu_cnt,
    -- 根据选课人数降序
    row_number() over (partition by class_id order by stu_cnt desc) as r
    from
    (
    -- 先统计每个班级, 每个课程的选课数量
    select
    class_id,
    course_id,
    count(1) as stu_cnt
    from
    t
    group by
    class_id,
    course_id
    ) as t1
    ) as t2
    -- 课程排第一的数据
    where r = 1;
    wxf666
        3
    wxf666  
       2022-08-03 08:43:56 +08:00   ❤️ 1
    『 SQLite 测试通过(排版原因,每行代码开头有 _ 和 全角空格)』

    WITH
      orig_data(class, stu, course) AS (
       VALUES
       ('1 班', '学生甲', '语文'), ('2 班', '戊', '语文'), ('3 班', '辛', '语文'),
       ('1 班', '学生乙', '语文'), ('2 班', '己', '语文'), ('3 班', '壬', '数学'),
       ('1 班', '学生丙', '数学'), ('2 班', '庚', '数学'), ('3 班', '癸', '数学'),
       ('1 班', '学生丁', '数学')
     ),

      table_with_rank(class, course, rank) AS (
       SELECT class, course, RANK() OVER (PARTITION BY class ORDER BY COUNT(*) DESC) rank
        FROM orig_data
    _   GROUP BY class, course
     )

    SELECT class, GROUP_CONCAT(course) courses
      FROM table_with_rank
    _WHERE rank = 1
    _GROUP BY class


    『结果(可能出现多个并列第一的课程,此时用「,」分隔)』

    class   courses
    ----- -------
      1 班 语文,数学
      2 班 语文
      3 班 数学
    UN2758
        4
    UN2758  
    OP
       2022-08-05 02:24:51 +08:00
    @sutra #1 这样只是把所有的情况列出来了吗,但是题目是要只列出每个班的最多人选的课
    UN2758
        5
    UN2758  
    OP
       2022-08-05 02:56:00 +08:00
    @reter #2 最后试来试去还是发现大佬的代码思路我比较能习惯
    UN2758
        6
    UN2758  
    OP
       2022-08-05 02:57:41 +08:00
    @reter #2 想请教一下为什么两个子查询的结果表为什么要给别名呢,似乎别名也没有用到啊
    wxf666
        7
    wxf666  
       2022-08-05 04:24:03 +08:00
    @UN2758 2 楼 3 楼都是同一个思路『窗口函数』吧。。

    都是计算出类似下表后,再取『排名 = 1 』的行:


    班级 课程 选课数 排名
    —— —— ——— ———
    1 班 语文   2     1
    1 班 数学   2     1
    2 班 语文   2     1
    2 班 数学   1     2
    3 班 数学   2     1
    3 班 语文   1     2
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1817 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 21ms · UTC 16:43 · PVG 00:43 · LAX 08:43 · JFK 11:43
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.