MySQL中的查询小技巧

心已赠人 2023-06-05 08:29 113阅读 0赞

## 数据表 ##

一个简单的示例表，user\_id为用户id外键，score为用户分数。  
![在这里插入图片描述][20191008193729755.png]

## 使用 CASE WHEN 自定义排序优先级 ##

用户分数查询系统中，首页列表按照**分数降序排列**，即原本应是如下顺序：  
![在这里插入图片描述][20191008194157954.png]  
  出于人性化考虑，当用户进入系统时，我们希望**该用户的所有得分都在最前面**显示，**且不影响其他记录的排序**。这时可以用`CASE WHEN`来自定义排序的优先级。

1.  写法①：简单`CASE`语句

-- WHEN语句中的值为判断条件，THEN语句中的值为优先级(越大优先级越高)
    SELECT * FROM demo ORDER BY CASE user_id WHEN 2 THEN 10 END DESC, score DESC;

1.  写法②：可搜索`CASE`语句

-- 相等判断
    SELECT * FROM demo ORDER BY CASE WHEN user_id = 2 THEN 10 END DESC, score DESC;
    -- 范围判断
    SELECT * FROM demo ORDER BY CASE WHEN (user_id > 1 AND user_id < 3) THEN 10 END DESC, score DESC;
    -- 相似判断
    SELECT * FROM demo ORDER BY CASE WHEN user_id LIKE "%2%" THEN 10 END DESC, score DESC;
    -- 包含判断
    SELECT * FROM demo ORDER BY CASE WHEN user_id IN (2) THEN 10 END DESC, score DESC;
    -- 正则判断
    SELECT * FROM demo ORDER BY CASE WHEN user_id REGEXP '[2]' THEN 10 END DESC, score DESC;

以上语句查询结果均为下图：  
![在这里插入图片描述][20191008194722357.png]

> 实际上，CASE还有很多妙用，用在`SELECT`作用域、用在`WHERE`作用域、用在`ORDER`作用域，都是可以大展身手的好地方。

-- 添加查询结果标记位，
    SELECT id, user_id, score, CASE WHEN score > 90 THEN '优秀' WHEN score > 80 THEN '合格' ELSE '不合格' END AS level FROM demo ORDER BY score DESC;

![在这里插入图片描述][20191008210007583.png]

-- 千人千面的判断，用户为VIP时，只要80就算优秀
    SELECT id, user_id, score, '优秀' AS level FROM demo WHERE CASE WHEN user_id in (2) THEN score > 80 ELSE score > 90 END ORDER BY score DESC;

![在这里插入图片描述][20191008210237902.png]

## 使用 FIELD 自定义排序方式 ##

开发中或多或少会有这样的需求，用户定义了排序的方式，而且并不一定在逻辑上有序。以本博客为例，我们需要按**user\_id 2  → \\rightarrow → 3  → \\rightarrow → 1**的顺序排列，**且不影响分数倒序的基础**，此时可以使用`FIELD`来自定义排序方式。

-- 单列的自定义排序
    SELECT * FROM demo ORDER BY FIELD(user_id, 2, 3, 1) ASC, score DESC;
    
    -- 多列的自定义排序
    SELECT * , CONCAT(score, user_id) as flag FROM demo ORDER BY FIELD(flag, "822", "812", "993", "893", "921", "751") ASC, created_at DESC

![在这里插入图片描述][20191008202625945.png]

> 注意：实际上，FIELD(str, str1, str2, str3) 返回str在比较列表中的排位，不在则返回0

## 使用 IF ELSE ##

`IF`函数也能定义一些方便的查询，此处不做赘述。

SELECT id, user_id, score, IF(score > 90, '优秀', '其他') AS level FROM demo ORDER BY score DESC;

![在这里插入图片描述][20191008210424663.png]

[20191008193729755.png]: /images/20230601/ec7b1289b3d24c37946968ea9435f9c3.png
[20191008194157954.png]: /images/20230601/0a2f9386d79a4f6cb3fd5c7f8059a622.png
[20191008194722357.png]: /images/20230601/d9a586cd9f6f4571aea9cf66c4cb7b7c.png
[20191008210007583.png]: /images/20230601/ba48d19789384988bd55d1a1b2c13c7b.png
[20191008210237902.png]: /images/20230601/14ab5c5a246a4dc9a3d7297b140f31a9.png
[20191008202625945.png]: /images/20230601/e8fa75d90da64068b181e597933bec71.png
[20191008210424663.png]: /images/20230601/429a8604bba14e4b9bd475183b8b6f70.png