mysql分组取前N条数据，mysql排名

àì夳堔傛蜴生んèń 2021-09-07 11:28 364阅读 0赞

CREATE TABLE `test` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(32) DEFAULT NULL,
      `score` int(11) DEFAULT NULL,
      `desc` varchar(32) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `name` (`name`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    
    INSERT INTO `test`.`test`(`id`, `name`, `score`, `desc`) VALUES (1, '雷欧', 80, '雷欧第一');
    INSERT INTO `test`.`test`(`id`, `name`, `score`, `desc`) VALUES (2, '迪迦', 70, '迪迦第三');
    INSERT INTO `test`.`test`(`id`, `name`, `score`, `desc`) VALUES (3, '雷欧', 20, '雷欧第三');
    INSERT INTO `test`.`test`(`id`, `name`, `score`, `desc`) VALUES (4, '赛罗', 90, '赛罗第一');
    INSERT INTO `test`.`test`(`id`, `name`, `score`, `desc`) VALUES (5, '雷欧', 30, '雷欧第二');
    INSERT INTO `test`.`test`(`id`, `name`, `score`, `desc`) VALUES (6, '迪迦', 100, '迪迦第一');
    INSERT INTO `test`.`test`(`id`, `name`, `score`, `desc`) VALUES (7, '迪迦', 80, '迪迦第二');
    INSERT INTO `test`.`test`(`id`, `name`, `score`, `desc`) VALUES (8, '赛罗', 50, '赛罗第二');
    INSERT INTO `test`.`test`(`id`, `name`, `score`, `desc`) VALUES (9, '赛罗', 10, '赛罗第三');

取出每个人前2的成绩记录

SELECT
    	t.* 
    FROM
    	test t 
    WHERE
    	2> ( SELECT count(*) FROM test WHERE name = t.name AND score > t.score ) 
    ORDER BY
    	t.name,t.score desc;
    	
    	
    
    SELECT
    	a.* 
    FROM
    	test a 
    WHERE
    	EXISTS ( SELECT count(*) as num FROM test WHERE NAME = a.NAME AND score > a.score HAVING num < 2 ) 
    ORDER BY
    	a.NAME,a.score desc;

以前只知道groupBy取max的那一条，取N条就不知道了，难理解，先记录一下。

下面是求排名的sql，用了mysql变量，记录一下，

第一种：并列排序

SELECT obj.id,
                   obj.userId,
                   obj.billMoney,
                   CASE
    
                       WHEN @rowtotal = obj.billMoney THEN
                           @rownum
                       WHEN @rowtotal := obj.billMoney THEN
                           @rownum := @rownum + 1
                       END AS rownum
            FROM (SELECT t.id,
                         t.userId,
                         t.billMoney
                  FROM tb_user_annual_report_2020 t
                  where t.billMoney != 0
                  ORDER BY t.billMoney DESC
                 ) AS obj
                    ,
                 (
                     SELECT @rownum := 0
                          , @rowtotal := NULL
                 ) r

声明两个用户变量rownum和rowtotal，：=是赋值运算符，初始值是0和null。

case when判断一个条件成立就直接结束了（这点和平时我们写if else有点区别），所以这里的意义是当金额等于rowtotal的时候名字rownum就取变量的值。当金额不等于rowtotal的时候就把金额赋值给rowtotal并且rownum+1。

所以这里的排序是病例排序，意思就是两个金额相同的排序号码是一样的。例1=1的时候排名 1 1 2 3 4 5

第二种：常规的，金额一样也往后排。例1=1的时候排名 1 2 3 4 5

SELECT
        obj.user_id,obj.score,@rownum := @rownum + 1 AS rownum
    FROM
        (
            SELECT
                user_id,
                score
            FROM
                `sql_rank`
            ORDER BY
                score DESC
        ) AS obj,
        (SELECT @rownum := 0) r

第三种：金额一样的排名一样，但是也占用一个名词位。例1=1的时候排名 1 1 3 4 5

SELECT
        obj_new.user_id,
        obj_new.score,
        obj_new.rownum
    FROM
        (
            SELECT
                obj.user_id,
                obj.score,
                @rownum := @rownum + 1 AS num_tmp,
                @incrnum := CASE
            WHEN @rowtotal = obj.score THEN
                @incrnum
            WHEN @rowtotal := obj.score THEN
                @rownum
            END AS rownum
            FROM
                (
                    SELECT
                        user_id,
                        score
                    FROM
                        `sql_rank`
                    ORDER BY
                        score DESC
                ) AS obj,
                (
                    SELECT
                        @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
                ) r
        ) AS obj_new