SQL查询单表数据之排序(二)

【SQL从一点一滴分析系列文章】为实际开发中的点点滴滴的总结,从最最简单的SQL 查询 到 综合分析查询
在分析 SQL 时,也会同时分析 mybatis 、Hibernate 中的相关操作
点击查看详情

本节讲述 对查询结果进行排序

1 以指定的次序返回查询结果

查询 用户表中所有用户的 姓名 年龄 地区,并按照年龄的升序排列,可以使用 order by 子句

select user_name,user_age,user_province 
	from t_user order by user_age asc 

使用 order by 子句,可以对结果集进行排序,默认情况下,order by 子句以升序方式排序,因此 asc 是可选的 ,desc 是表示降序的。
上述查询的结果,也可以这样写

select user_name,user_age,user_province 
	from t_user order by 2 asc 

这里使用了编号来实现排序的,那么在这里使用的2,是因为在我们的查询结果集中 user_name 占第1列,user_age 占第二列,这个编号是从1开始,依次从左到右依次对应查询结果集的每一列,如这里的2对应的就是 user_age。

2 按多个字段排序

查询 用户表中所有用户的 姓名 年龄 地区,并按照用户的编号升序排列,然后再按年龄的降序排列,我们可以如下写

select user_name,user_age,user_province ,user_id
	from t_user order by user_id asc ,user_age desc

在 order by 子句中列出不同的排列,可使用逗号分隔,在这里优先次序是从左到右。

3 按子串排序

查询用户表中所有的用户的姓名 年龄 地区详情,并且按照地区详情的前四个字符排序

在 DB2 MySQL Oracle PostgreSQL 中可以这样写

select user_name,user_age,user_address 
	from t_user order by substr(user_address,1,4)

substr()函数和hibernate的substr()参数都一样

substr(string string,num start,num length);
参数一 string为字符串;
参数二 start为起始位置;
参数三 length为长度。

所以在这里,substr(user_address,1,4),例如查出来的地址是 “山西省太原市八堡” ,通过 substr(user_address,1,4) 取出的标准就是 “山西省太”。
在 SQL Server 中可以这样写

select user_name,user_age,user_address 
	from t_user order by substring(user_address,1,4)

需要注意的是,这里的角标是从 1 开始的,在hibernate中substr()的start是从0开始的。

4 对字母数字混合的数据排序

对现有字母和数字的混合数据,希望按照数字或字符部分来rd排序
例如 我们查询用户的 姓名 编号 ,并将 姓名编号连接在一起返回到结果 集中

select user_name,user_id ,CONCAT(user_name,"",user_id) as msg  from t_user 

在这里插入图片描述

在 Oracle 和 PostgreSQL 中可以使用 函数 replace 和 translate 函数来修正 要排序的字条串,例如我们这里把查询到的结果集 msg 中的所有数字替换掉然后进行排序

select user_name,user_id ,CONCAT(user_name,"",user_id) as msg  from t_user 
order by
	 replace(msg,
				replace(
				    translate(msg,'0123456789','#########'),
                   '#',
                   ''),
            '')

使用的函数为replace()
含义为:替换字符串
replace(原字段,“原字段旧内容“,“原字段新内容“,)

translate 与replace类似是替换函数,但translate是一次替换多个单个的字符。

我们这里把查询到的结果集 msg 中的所有的数字删除掉然后进行排序

select user_name,user_id ,CONCAT(user_name,"",user_id) as msg  from t_user 
order by
 replact( translate(msg,'0123456789','#'),'#','')

在 MySQL 和 SQL Server 中不支持 translate 函数。
replace 和 translate 函数从每一行中去掉数字或字符,这样就可以很容易的来进行排序。

5 处理排序中空值问题

例如我们在查询用户表的信息时,当根据用户的年龄来排序时,用户的年龄有空值,那么此时我们就需要处理这种情况,可以将空值的排列在最后,可以通过如下方式来处理

5.1 查询一
select user_name,user_age,user_id 
	from t_user

user_nameuser_ageuser_id
ceshi128201
ceshi3204
ceshi422198
ceshi5177
ceshi640110
5. 2 查询二

DB2 MySQL PostgreSQL 和 SQL Server
使用 case 表达式来标记 一个值是否为 NULL ,这个标记有两个值,一个表示 NULL, 一个表示非 NULL 。这样只要在 order by 子句中增加标记列,便可以很容易的控制空值 是排列在前面还是最后面

select user_name,user_age,user_id 
	from ( select user_name,user_id,user_age,
	               case when user_age is null then 0
	               else 1
	               end
	               as is_null
          ) x
   order by is_null desc,user_age

user_nameuser_ageuser_id
ceshi322198
ceshi328201
ceshi340110
ceshi3177
ceshi5204

在上述查询排序中,如果 user_age 为 null,那么标识列 is_null 的值为 0,否则为1,那么在 order by 中先 根据 is_null 倒序排列,所以 user_age 为null 是排列在最后的,然后再根据 user_age 进行二次排列

5. 3查询三
select user_name,user_age,user_id 
	from ( select user_name,user_id,user_age,
	               case when user_age is null then 0
	               else 1
	               end
	               as is_null
          ) x
   order by is_null desc,user_age desc

user_nameuser_ageuser_id
ceshi340110
ceshi328201
ceshi322198
ceshi3177
ceshi5204

在这里与查询二 中的区别是 在 order by 中,user_age 又采用了倒序

5.4 查询四
select user_name,user_age,user_id 
	from ( select user_name,user_id,user_age,
	               case when user_age is null then 0
	               else 1
	               end
	               as is_null
          ) x
   order by is_null asc,user_age asc

user_nameuser_ageuser_id
ceshi3177
ceshi5204
ceshi322198
ceshi328201
ceshi340110
6 根据数据项的键排序

在实际应用中,需要根据某些条件逻辑来排序,例如查询 用户表数据,user_province 如果是 北京 就用用户 user_age 来排序,否则根据 user_id 来排序
要实现这样的查询,我们可以在 order by 子句中使用 case 表达式

select user_name,user_age,user_id,user_province 
from t_user
order by case when user_province='北京' then user_age
         else user_id
         end

完结

早起的年轻人 CSDN认证博客专家 移动开发 项目管理 Java
只要用心去做,每一件事情还是有可能成功的,当然成功是没有界限的,只不过是达到自己心里的那个目标,公众号:我的大前端生涯,一个爱喝茶的程序员,通常会搞搞SpringBoot 、Herbinate、Mybatiys、Android、iOS、Flutter、Vue、小程序等.
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页