SQL查询结果数据排序（二）

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

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

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_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

在这里与查询二 中的区别是 在 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

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

完结