数据表
一个简单的示例表,user_id为用户id外键,score为用户分数。
使用 CASE WHEN 自定义排序优先级
用户分数查询系统中,首页列表按照分数降序排列,即原本应是如下顺序:
出于人性化考虑,当用户进入系统时,我们希望该用户的所有得分都在最前面显示,且不影响其他记录的排序。这时可以用CASE WHEN
来自定义排序的优先级。
- 写法①:简单
CASE
语句
-- WHEN语句中的值为判断条件,THEN语句中的值为优先级(越大优先级越高)
SELECT * FROM demo ORDER BY CASE user_id WHEN 2 THEN 10 END DESC, score DESC;
- 写法②:可搜索
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;
以上语句查询结果均为下图:
实际上,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;
-- 千人千面的判断,用户为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;
使用 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
注意:实际上,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;