目录
一、基于java排序和排名
1、数值相同,排名相同,排名连续
2、数值相同,排名相同,排名不连续
3、数值相同,排名不相同,排名连续
二、基于mysql排序和排名
1、准备一张表
2、插入数据
3、设置临时变量,方便后续查询
4、数值相同,排名相同,排名连续
5、数值相同,排名相同,排名不连续
6、数值相同,排名不相同,排名连续
一、基于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
) r6、数值相同,排名不相同,排名连续
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
