复杂sql

一、按分数统计排名查询怎么写，比如
名次  分数
  1      90
  2      87
  2      87
  4      80
  5      79

sql:select data.*

    , @r1:=@r1+1
    , @r:=if(@p=value,@r,@r1) as 排名
    , @p:=value
from(
select 100 as value union all
select 100 as value union all
select 90 as value union all
select 80 as value union all
select 80 as value union all
select 70 as value
) data,(select @r:=0) as b,(select @r1:=0) as c, (select @p:=null) as d

order by value desc;

二、从mem_member中查询数据更新cms_terminal_user 

UPDATE cms_terminal_user ctu

INNER JOIN mem_member mm ON ctu.USER_MOBILE_NO = mm.Mobile
SET ctu.USER_TYPE = 2,
 ctu.USER_CARD_NO = mm.IDNum
WHERE
mm.Mobile != ''
AND mm.Mobile IS NOT NULL
AND mm.IDNum != ''

AND mm.IDNum IS NOT NULL

三、Mysql 查询实现成绩排名，相同分数名次相同，类似于rank()函数

1、sql查询实现

测试如下：

mysql> select * from score ;
+----------+--------------+---------------------+--------------+-------+
| study_no | student_name | subject_id          | subject_name | score |
+----------+--------------+---------------------+--------------+-------+
| student1 | student1     | CodeCourseSubject_0 | 语文         |   120 |
| student2 | student2     | CodeCourseSubject_0 | 语文         |   110 |
| student3 | student3     | CodeCourseSubject_0 | 语文         |   110 |
| student4 | student4     | CodeCourseSubject_0 | 语文         |    80 |
| student5 | student5     | CodeCourseSubject_0 | 语文         |    81 |
| student1 | student1     | CodeCourseSubject_2 | 英语         |   150 |
| student2 | student2     | CodeCourseSubject_2 | 英语         |   130 |
| student3 | student3     | CodeCourseSubject_2 | 英语         |   130 |
| student4 | student4     | CodeCourseSubject_2 | 英语         |    44 |
| student5 | student5     | CodeCourseSubject_2 | 英语         |    45 |
+----------+--------------+---------------------+--------------+-------+
10 rows in set

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16

首先这里对科目显示进行了行列转换，并且对以学号study_no进行分组计算总分，并且按排名排序， 
sql如下：

SELECT @rownum:=@rownum+1 AS rownum,
　　　if(@total=total,@rank,@rank:=@rownum)as rank,
　　　@total:=total,
　　　A.* 
　　　FROM　(SELECT study_no AS studyNo,
　　　　　　　       student_name AS studentName,
　　　　　　　       SUM(score) AS total,
　　　　　　　       SUM(IF(subject_id='CodeCourseSubject_0',score,0)) AS 语文,
　　　　　　　       SUM(IF(subject_id='CodeCourseSubject_2',score,0)) AS 英语 
　　　　　　　       FROM score GROUP BY study_no ORDER BY total DESC
　　　　　　　)A,(SELECT @rank:=0,@rownum:=0,@total:=null)B

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11

结果：

+--------+------+---------------+----------+-------------+-------+-------+-------+
| rownum | rank | @total:=total | studyNo  | studentName | total | 语文  | 英语  |
+--------+------+---------------+----------+-------------+-------+-------+-------+
|      1 |    1 |         270.0 | student1 | student1    | 270.0 | 120.0 | 150.0 |
|      2 |    2 |         240.0 | student2 | student2    | 240.0 | 110.0 | 130.0 |
|      3 |    2 |         240.0 | student3 | student3    | 240.0 | 110.0 | 130.0 |
|      4 |    4 |         126.0 | student5 | student5    | 126.0 |  81.0 |  45.0 |
|      5 |    5 |         124.0 | student4 | student4    | 124.0 |  80.0 |  44.0 |
+--------+------+---------------+----------+-------------+-------+-------+-------+
5 rows in set

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

可见排名第二名有两个相同的分数，后面的排名自动往后移。

下面提供另外一种在程序中实现的方法：

2、程序实现

首先获得全班总分并且以降序排序

public List<Object[]> findAllTotalScore() {
        String sql = "SELECT study_no,sum(score) AS total from score s ";
        javax.persistence.Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

• 1
• 2
• 3
• 4
• 5

对获得的总分信息进行排序和封装成map(study_no，rank)

private Map<String, Long> rank(List<Object[]> objects) {
        long previousRank = 1;
        double total = 0;
        //记录排名的map，map<study_no，排名>
        Map<String, Long> rankMap = new HashMap<>();
        for (int i = 0; i < objects.size(); i++) {
            Object[] object = objects.get(i);
            //计算名次，相同分数排名一样
            if (total == (double) object[1]) {
                rankMap.put(object[0].toString(), previousRank);
            } else {
                rankMap.put(object[0].toString(), i + 1L);
                total = (double) object[1];
                previousRank = i + 1;
            }
        }
        return rankMap;
    }

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18

使用map直接根据学号就可以获得成绩排名。