• MySql8开窗函数


    测试数据表:

    CREATE TABLE `school_score` (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` char(1) DEFAULT NULL,
      `course` char(10) DEFAULT NULL,
      `score` int DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
    INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (1, 'A', 'Chinese', 80);
    INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (2, 'B', 'Chinese', 90);
    INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (3, 'C', 'Chinese', 70);
    INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (4, 'A', 'Math', 70);
    INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (5, 'B', 'Math', 100);
    INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (6, 'C', 'Math', 80);
    INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (7, 'A', 'English', 90);
    INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (8, 'B', 'English', 85);
    INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (9, 'C', 'English', 99);

    开窗函数排名 row_number () over (partition by ) rank 作为关键字 不能用于别名

    SELECT name,course,score score,
    row_number( ) over (PARTITION by course order by score desc)  as score_rank
    from school_score;

    查询各科成绩第一的人 或者前两名

    SELECT * from (
    select name,course,score ,row_number() over (PARTITION by course order by score) as score_rank from school_score
    ) as a  where a.score_rank in (1,2);

    不公平排序 会跳过重复的序号

    select name,course,rank() over (order by score) as score_rank from school_score;

    公平排序 序号连续 但是会并列序号 11 11 

    select name,course,score,dense_rank() over (order by score) as score_rank from school_score;

    row_number 排序 不会出现并列 1 2 3

    select name,course,score, row_number() over (order by score) as score_rank from school_score;

    分桶,为结果集按行数分成不同的桶,比如 ntile(2) 就对结果集分分两组 nitil(3) 分三组,以此类推,如果分的组或者桶数不均,第一组或第一桶则数量加1

    不均等

    select name,course,score,  ntile(4) over(order by score desc) as score_rank from school_score;

    均等

    select name,course,score,  ntile(3) over(order by score desc) as score_rank from school_score;
  • 相关阅读:
    bt5设置IP
    flyCoding
    [Cocoa][译]苹果 Cocoa 编码规范中文版
    [BZOJ4569] [Scoi2016]萌萌哒
    BZOJ4899]记忆的轮廓
    [BZOJ1701] [Usaco2007 Jan]Cow School牛学校
    [Poi2011]Lightning Conductor
    [BZOJ4709] [Jsoi2011] 柠檬
    决策单调性优化dp 专题练习
    2369. 区间
  • 原文地址:https://www.cnblogs.com/coderdxj/p/14361073.html
Copyright © 2020-2023  润新知