java和mysql进行排序和排名

这人很懒没留下什么

已于 2023-04-11 15:38:02 修改

阅读量863

点赞数 2

文章标签：排序算法算法 java mysql

于 2023-04-07 15:20:41 首次发布

本文链接：https://blog.csdn.net/qq_35160479/article/details/130010737

版权

一、基于java排序和排名

1、数值相同,排名相同,排名连续

package com.saas.demo;

import lombok.Data;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;

public class RankDemo {

    public static void main(String[] args) {
        //数据初始化
        List<People> peopleList = new ArrayList<>();
        peopleList.add(new People("张三",20));
        peopleList.add(new People("李四",30));
        peopleList.add(new People("王五",25));
        peopleList.add(new People("赵六",21));
        peopleList.add(new People("钱七",25));
        peopleList.add(new People("田八",30));
        peopleList.add(new People("周九",26));
        peopleList.add(new People("孙十",27));
        //按年龄从大到小排序
        peopleList = peopleList.stream().sorted(Comparator.comparing(People::getAge).reversed()).collect(Collectors.toList());

        int a =0;
        int b=Integer.MIN_VALUE;
        for(People people:peopleList){
            if(!people.getAge().equals(b)){
                b=people.getAge();
                a = a+1;
            }
            people.setRank(a);
        }
        //输出结果
        peopleList.stream().forEach(System.out::println);

    }
}

@Data
class People{
    //姓名
    private String name;
    //年龄
    private Integer age;
    //排名
    private Integer rank;

    public People(String name, Integer age) {
        this.name = name;
        this.age = age;
    }
}

2、数值相同,排名相同,排名不连续

package com.saas.demo;

import lombok.Data;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;

public class RankDemo {

    public static void main(String[] args) {
        //数据初始化
        List<People> peopleList = new ArrayList<>();
        peopleList.add(new People("张三",20));
        peopleList.add(new People("李四",30));
        peopleList.add(new People("王五",25));
        peopleList.add(new People("赵六",21));
        peopleList.add(new People("钱七",25));
        peopleList.add(new People("田八",30));
        peopleList.add(new People("周九",26));
        peopleList.add(new People("孙十",30));
        //按年龄从大到小排序
        peopleList = peopleList.stream().sorted(Comparator.comparing(People::getAge).reversed()).collect(Collectors.toList());

        int a =0;
        int b=Integer.MIN_VALUE;
        int c=0;
        for(People people:peopleList){
            if(!people.getAge().equals(b)){
                b=people.getAge();
                a = a+1+c;
            }else {
                c++;
            }
            people.setRank(a);
        }
        //输出结果
        peopleList.stream().forEach(System.out::println);

    }
}

@Data
class People{
    //姓名
    private String name;
    //年龄
    private Integer age;
    //排名
    private Integer rank;

    public People(String name, Integer age) {
        this.name = name;
        this.age = age;
    }
}

3、数值相同,排名不相同,排名连续

package com.saas.demo;

import lombok.Data;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

public class RankDemo {

    public static void main(String[] args) {
        //数据初始化
        List<People> peopleList = new ArrayList<>();
        peopleList.add(new People("张三",20));
        peopleList.add(new People("李四",30));
        peopleList.add(new People("王五",25));
        peopleList.add(new People("赵六",21));
        peopleList.add(new People("钱七",25));
        peopleList.add(new People("田八",30));
        peopleList.add(new People("周九",26));
        peopleList.add(new People("孙十",30));
        //按年龄从大到小排序
        peopleList = peopleList.stream().sorted(Comparator.comparing(People::getAge).reversed()).collect(Collectors.toList());

        AtomicInteger a = new AtomicInteger(1);
        peopleList.stream().forEach(s->s.setRank(a.getAndIncrement()));
        //输出结果
        peopleList.stream().forEach(System.out::println);

    }
}

@Data
class People{
    //姓名
    private String name;
    //年龄
    private Integer age;
    //排名
    private Integer rank;

    public People(String name, Integer age) {
        this.name = name;
        this.age = age;
    }
}

二、基于mysql排序和排名

1、准备一张表

CREATE TABLE `people` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL COMMENT '姓名',
`age` int(11) NOT NULL COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

2、插入数据

INSERT INTO people (`id`, `name`, `age`) VALUES (1, '张三', 20);
INSERT INTO people (`id`, `name`, `age`) VALUES (2, '李四', 30);
INSERT INTO people (`id`, `name`, `age`) VALUES (3, '王五', 25);
INSERT INTO people (`id`, `name`, `age`) VALUES (4, '赵六', 21);
INSERT INTO people (`id`, `name`, `age`) VALUES (5, '钱七', 25);
INSERT INTO people (`id`, `name`, `age`) VALUES (6, '田八', 30);
INSERT INTO people (`id`, `name`, `age`) VALUES (7, '周九', 26);
INSERT INTO people (`id`, `name`, `age`) VALUES (8, '孙十', 30);

3、设置临时变量,方便后续查询

set @rownum=0;

4、数值相同,排名相同,排名连续

 SELECT
			temp.name,
            temp.age,
            CASE
                WHEN @rowtotal = age THEN
                @rownum
                WHEN @rowtotal := age THEN
                @rownum :=@rownum + 1
                WHEN @rowtotal = 0 THEN
                @rownum :=@rownum + 1
        END AS rownum
        FROM
            (
                SELECT
                    name,age
                FROM
                people
                ORDER BY
                age DESC
                ) AS temp,
        (SELECT @rownum := 0 ,@rowtotal := NULL) r

5、数值相同,排名相同,排名不连续

SELECT
            temp.name,
            temp.age,
            @rownum := @rownum + 1 AS value,#临时变量
            @incrnum := CASE
        WHEN @rowtotal = temp.age THEN
            @incrnum
        WHEN @rowtotal := temp.age THEN
            @rownum
        END AS rownum
        FROM
            (
                SELECT
                     name,age
                FROM
                  people
                ORDER BY
                    age DESC
            ) AS temp,
            (
                SELECT
                    @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
            ) r

6、数值相同,排名不相同,排名连续

 SELECT
			temp.name,
            temp.age,
            @rownum :=@rownum + 1 AS rownum
        FROM
            (
                SELECT
                    name,age
                FROM
                people
                ORDER BY
                age DESC
                ) AS temp,
        (SELECT @rownum := 0 ,@rowtotal := NULL) r