数据源
empid deptid salary
1 10 5500.00
2 20 4800.00
3 40 14500.00
4 40 44500.00
5 50 6500.00
6 50 7500.00
7 10 4500.00
8 40 6500.00
9 20 1900.00
输出:(按照部门deptid分组,对工资进行降序,算出排名)
empid deptid salary rank
1 10 5500.00 1
7 10 4500.00 2
2 20 4800.00 1
9 20 1900.00 2
4 40 44500.00 1
3 40 14500.00 2
8 40 6500.00 3
6 50 7500.00 1
5 50 6500.00 2
通过编写JAVA代码排序算法实现,不能通过JDBC编写SQL实现。
代码:
package com;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
class User {
private int empid;
private int deptid;
private Double salary;
public User(){}
public User(int empid, int deptid, Double salary) {
this.empid = empid;
this.salary = salary;
this.deptid = deptid;
}
public int getEmpid() {
return empid;
}
public void setEmpid(int empid) {
this.empid = empid;
}
public int getDeptid() {
return deptid;
}
public void setDeptid(int deptid) {
this.deptid = deptid;
}
public Double getSalary() {
return salary;
}
public void setSalary(Double salary) {
this.salary = salary;
}
}
class MyComparator implements Comparator {
@Override
public int compare(Object o1, Object o2) {
if (o1.getClass() == User.class && o2.getClass() == User.class) {
if (((User) o1).getDeptid() > ((User) o2).getDeptid()) {
return 1;
} else if (((User) o1).getDeptid() == ((User) o2).getDeptid()) {
if (((User) o1).getSalary() > ((User) o2).getSalary()) {
return -1;
} else if (((User) o1).getSalary() == ((User) o2).getSalary()) {
return 0;
}
{
return 1;
}
} else {
return -1;
}
} else {
return 0;
}
}
}
public class test {
public static void main(String[] args) {
List<User> userList = new ArrayList<>();
userList.add(new User(1, 10, 5500.00));
userList.add(new User(2, 20, 4800.00));
userList.add(new User(3, 40, 14500.00));
userList.add(new User(4, 40, 44500.00));
userList.add(new User(5, 50, 6500.00));
userList.add(new User(6, 50, 7500.00));
userList.add(new User(7, 10, 4500.00));
userList.add(new User(8, 40, 6500.00));
userList.add(new User(9, 20, 1900.00));
Collections.sort(userList, new MyComparator());
int rank=1;
int temp=0;
for (User user : userList) {
if(temp==user.getDeptid()){
rank++;
}else{
rank=1;
}
temp=user.getDeptid();
System.out.println(user.getEmpid() + "\t\t" + user.getDeptid() + "\t\t" + user.getSalary() + "\t\t" + rank);
}
}
}