MySQL技巧(CASE和排名功能)

CASE WHEN

我们通常会用一些数字来代替一些枚举，比如性别、状态等，但如果我们想在SQL语句中用上一些条件语句的话，这个时候就可以用case when,例如我们如果想通过一些查询出来的状态值直接转化成他们的所表达的意思，像sex的0代表保密，1代表男，2代表女这样类似，我们这个时候就可以在select后面添加case片段，case when分为简单函数和搜索函数。

简单函数格式

case  case_value
    when condition then result
    when condition then result
    when condition then result
else result
end

搜索函数格式

case  
    WHEN Boolean_expression THEN result_expressio
    [WHEN Boolean_expression THEN result_expressio]
    else result
end

示例

• student 表

id	student_name	sex	age
1	小明	1	18
2	小李	2	20
3	小黑	0	19

• 根据sex的值返回其实际意思

  SELECT 
  id, student_name, sex, age,
  ( CASE sex 
    WHEN 1 THEN "男" 
    WHEN 2 THEN "女" 
     ELSE "保密" 
   END ) AS "sex_name" 
  FROM `student`
  

  id	student_name	sex	age	sex_name
  1	小明	1	18	男
  2	小李	2	20	女
  3	小黑	0	19	保密

• 区分用户年龄类型

  SELECT
    id,
    student_name,
    sex,
    age,
    ( CASE WHEN age <= 18 THEN "少年" WHEN age > 18 && age < 20 THEN "年轻人" ELSE "神秘人" END ) AS "user_type" 
  FROM student
  

  id	student_name	sex	age	user_type
  1	小明	1	18	少年
  2	小李	2	20	神秘人
  3	小黑	0	19	年轻人

排名函数

参考文献

有的时候我们遇到一些场景让我们通过某些属性做一些排名，简单的排名可以通过排序来完成，但是有些比较特殊的排名需要用到mysql提供的相关函数来进行排序(需要MySQL 8+才可以使用)。

函数	作用
RANK	并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。
DENSE_RANK	并列连续排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。
ROW_NUMBER	连续排名，即使相同的值，依旧按照连续数字进行排名。

连续排名ROW_NUMBER

SELECT *,
ROW_NUMBER() OVER (ORDER BY score DESC) ranking
FROM student;

• 变量实现

  SELECT s.score, (@cur_rank := @cur_rank + 1) ranking
  FROM student s, (SELECT @cur_rank := 0) r
  ORDER BY score DESC;
  

score	ranking
94	1
94	2
88	3
83	4
83	5
72	6

并列连续排名DENSE_RANK

SELECT score,
DENSE_RANK() OVER(ORDER BY score DESC) 
FROM student;

• 使用变量和IF语句实现

  SELECT
    s.score,
  IF
    ( @pre_score = s.score, @cur_rank, @cur_rank := @cur_rank + 1 )         ranking
  FROM
    student s,
    ( SELECT @cur_rank := 0, @pre_score = NULL ) r 
  ORDER BY
    s.score DESC;
  

• 使用变量和CASE语句实现

  SELECT
    s.score,
    (
    CASE
            
            WHEN @pre_score = s.score THEN
            @cur_rank 
            WHEN @pre_score := s.score THEN
            @cur_rank := @cur_rank + 1 
        END 
        ) ranking 
    FROM
        student s,
        ( SELECT @cur_rank := 0, @pre_score = NULL ) r 
  ORDER BY
    s.score DESC;
  

score	ranking
94	1
94	1
88	2
83	3
83	3
72	4

并列跳跃排名RANK

SELECT score,
RANK() OVER(ORDER BY score DESC)
FROM score;

• 使用变量和IF语句实现

  SELECT
    s.score,
    @rank_counter := @rank_counter + 1,
  IF
    ( @pre_score = s.score, @cur_rank, @cur_rank := @rank_counter ) ranking,
    @pre_score := s.score 
  FROM
    student s,
    ( SELECT @cur_rank := 0, @pre_score := NULL, @rank_counter := 0 ) r 
  ORDER BY
    s.score DESC;
  

• 使用变量和CASE语句实现

  SELECT
    s.score,
    @rank_counter := @rank_counter + 1,
    ( CASE WHEN @pre_score = s.score THEN @cur_rank WHEN @pre_score := s.score THEN @cur_rank := @rank_counter END ) ranking 
  FROM
    student s,
    ( SELECT @cur_rank := 0, @pre_score := NULL, @rank_counter := 0 ) r 
  ORDER BY
    s.score DESC;
  

score	@rank_counter := @rank_counter + 1	ranking
94	1	1
94	2	1
88	3	3
83	4	4
83	5	4
72	6	6