排序

order by后面可以根多个字段及其升降序，如果为标明则MySQL采用默认升序的策略进行排序。

对字符串用默认的升降序排序得到的是按字典序排列的结果

默认排序

升序

order by id asc

降序

order by id desc

自定排序

利用field()函数，来指定排序顺序

order by field(id, 5, 2, 3)

下面是一段示例，SQL语句与对应的Mybatis映射。

SELECT * FROM project_case where
case_id in (119, 113, 117)
order by field(case_id, 119, 113, 117);

<select id="selectSortedProjectCaseByIds" parameterType="String" resultMap="ProjectCaseResult">
    SELECT * FROM project_case where
    case_id in
    <foreach collection="caseIdList" item="caseId" open="(" close=")" separator=",">
        #{caseId}
    </foreach>
    order by field
    <foreach collection="caseIdList" item="caseId" open="(case_id," close=")" separator=",">
        #{caseId}
    </foreach>
</select>

List<ProjectCase> selectSortedProjectCaseByIds(@Param("caseIdList") List<Integer> caseIdList);

字符串排序

SELECT * FROM `tasks` order by FIELD(state, 'ready', 'failure', 'success'), execute_time desc

1. cast( 要排序的字段名 as integer) ，注意 integer 小写

   整数和小数都可以处理

2. to_number(要排序的字段名)

3. order by to_number(regexp_substr(salary,'[0-9]*[0-9]',1)) desc，regexp_substr 为截取方法，1为起始位置

其中，正则表达式函数

REGEXP_SUBSTR(srcstr, pattern, position, occurrence, modifier)

• srcstr ：需要进行正则处理的字符串

• pattern ：进行匹配的正则表达式

• position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）

• occurrence ：标识第几个匹配组，默认为1

• modifier ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

排序窗口函数

rank() over

作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。 说明：例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。即：1 1 3 4 5 5 7

select a.Score as Score,
(select count(b.Score) + 1 from Scores b where b.Score > a.Score) as Rank
from Scores a
order by a.Score DESC

dense_rank() over

作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。 说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6

select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC

row_number() over

作用：查出指定条件后的进行排名，条件相同排名也不相同，排名间断不连续。 说明：这个函数不需要考虑是否并列，即使根据条件查询出来的数值相同也会进行连续排序。即：1 2 3 4 5 6

ntile(${group_num}) over

Ntile() 将所有记录分成group_num个组，每组序号一样

用于做分位数，如4分位数，可以把数组排序后分成4个部分，每个部分的序号相同

select *, ntile(2) OVER(order by number ) as row_num
from num 

使用小提示

dense_rank() over 后面跟排序的依据的列，下面是用了一个排序好的列(order by score desc)。 注意：如果select中有一列是用rank()这类函数，其他的列都会按着他这列规定好的顺序排。

select score, dense_rank() over (order by score desc) as 'rank'  #这个rank之所以要加引号，因为rank本身是个函数，直接写rank会报错
from scores;

1. 刘志钊 74
2. 刘昊 84
3. 陈钱 89
4. 段佳宁 90
5. 张恒为 93
6. jiangjinbo 95
7. 刘利明 100
8. yuangege 101
9. 臧家名 110
10. 谭善如 116
11. xuna 125
12. tangguoyao 155
13. liaomoran 156
14. guqiyuan 170
15. houyihang 178
16. 乔畅 204
17. 整重阳 237