业务需求:
先按参与人数降序,再按排名之和升序
人数:perTotal ,排名之和:rankTotal
完整代码:
@Override
public JSONObject getTopUnitOfGameRanking(List<Map<Long, Integer>> userMap,
int limit) {
JSONObject json = new JSONObject();
List<JSONObject> list = new ArrayList<JSONObject>();
List<JSONObject> lists = new ArrayList<JSONObject>();
List<Long> userIds = new ArrayList<Long>();
for(Map<Long, Integer> userRanking : userMap){
for(Long userId : userRanking.keySet()){
userIds.add(userId);
}
}
if (userIds != null && userIds.size() > 0) {
List<Object> param = new ArrayList<Object>();
StringBuilder builder = new StringBuilder();
builder.append("select unit_id,count(user_id) as pertotal from t_user_expand where unit_id !=0 and user_id in ( ");
for (int i = 0; i < userIds.size(); i++) {
if (i != 0) {
builder.append(",");
}
builder.append("?");
param.add(userIds.get(i));
}
builder.append(" ) GROUP BY unit_id order by pertotal desc limit ? ");
param.add(limit);
//final List<Long> unitIds = new ArrayList<Long>();
list = primaryJdbcTemplate.query(builder.toString(), param.toArray(),new RowMapper<JSONObject>() {
@Override
public JSONObject mapRow(ResultSet rs, int rowNum) throws SQLException {
JSONObject o = new JSONObject();
o.put("unitId", rs.getLong("unit_id"));
o.put("perTotal", rs.getLong("pertotal"));
return o;
}
});
List<Object> params = new ArrayList<Object>();
StringBuilder builders = new StringBuilder();
builders.append("select unit_id,user_id from t_user_expand where unit_id !=0 and user_id in( ");
for (int i = 0; i < userIds.size(); i++) {
if (i != 0) {
builders.append(",");
}
builders.append("?");
params.add(userIds.get(i));
}
builders.append(" ) limit ? ");
params.add(limit);
lists = primaryJdbcTemplate.query(builders.toString(), params.toArray(),new RowMapper<JSONObject>() {
@Override
public JSONObject mapRow(ResultSet rs, int rowNum) throws SQLException {
JSONObject o = new JSONObject();
o.put("unitId", rs.getLong("unit_id"));
o.put("user_id", rs.getLong("user_id"));
return o;
}
});
for(JSONObject obj : lists){ // 遍历 “用户id”关联的“府id”
for(Map<Long, Integer> user : userMap){ //由“用户id”获取对应的排名
Integer rank =user.get(obj.get("user_id"));
if(null == rank){
rank = 0;
}
for(JSONObject object : list){
if(((Long)object.get("unitId")).compareTo((Long)obj.get("unitId"))==0){
Integer oldRankTotal = (Integer)object.get("rankTotal");
if(null == oldRankTotal){
oldRankTotal = 0;
}
object.put("rankTotal", oldRankTotal + rank);
}
}
}
}
}
Collections.sort(list, new Comparator<JSONObject>(){
/**
* 先按参与人数降序,再按排名之和升序
*/
@Override
public int compare(JSONObject o1, JSONObject o2) {
Long perTotal1 = (Long)o1.get("perTotal");
Long perTotal2 = (Long)o2.get("perTotal");
Integer rankTotal1 = (Integer)o1.get("rankTotal");
Integer rankTotal2 = (Integer)o2.get("rankTotal");
if(perTotal1.compareTo(perTotal2) > 0){
return -1;
}else if(perTotal1.compareTo(perTotal2) < 0){
return 1;
}else{
if(rankTotal1.compareTo(rankTotal2) > 0){
return 1;
}else if(rankTotal1.compareTo(rankTotal2) < 0){
return -1;
}else{
return 0;
}
}
}
});
json.put("data", list);
return json;
}
关键部分代码(在人数相同的情况下,比较排名之和):
List<JSONObject> list = new ArrayList<JSONObject>();
Collections.sort(list, new Comparator<JSONObject>(){
/**
* 先按参与人数降序,再按排名之和升序
*/
@Override
public int compare(JSONObject o1, JSONObject o2) {
Long perTotal1 = (Long)o1.get("perTotal");
Long perTotal2 = (Long)o2.get("perTotal");
Integer rankTotal1 = (Integer)o1.get("rankTotal");
Integer rankTotal2 = (Integer)o2.get("rankTotal");
if(perTotal1.compareTo(perTotal2) > 0){
return -1;
}else if(perTotal1.compareTo(perTotal2) < 0){
return 1;
}else{
if(rankTotal1.compareTo(rankTotal2) > 0){
return 1;
}else if(rankTotal1.compareTo(rankTotal2) < 0){
return -1;
}else{
return 0;
}
}
}
});