SQL 汉字笔画排序 音序排序

Order by排序查询

---

升序排列asc

select * from 职工 order by 工资 asc

降序排列desc

select * from 职工 order by 工资 desc

---

汉字笔画排序

select * from 职工 order by 姓名 collate chinese_prc_stoke_cs_as_ks_ws asc

音序排序(abc)

select * from 职工 order by 姓名 collate chinese_prc_cs_as asc

---

多列排序

–例：先工资降序，再职工ID升序

select * from 职工 order by 工资 desc, 职工ID asc

---

随机排序

select * from 职工 order by newid()

tip：newid()会先扫描整表，产生计算列，再排序，故不要用于较大数据表

---

显示部分记录的排序top

• 例1：显示工资最高的前3条

select top 3 * from 职工 order by 工资 desc

• 例2：显示工资最低的前20%

select top 20 percent * from 职工 order by 工资 asc

• 例3：随机显示3条

select top 3 * from 职工 order by newid()

区别：

• Oracle：虚拟列rownum

select * from (select * from 职工 order by 工资 desc) where rownum<=3

• DB2：

select * from 职工 order by 工资 desc fetch first 3 rows only

---

计算字段排序

例1：按虚拟字段“奖金”从高到底排序

select 姓名，工资，工资*0.08 as '奖金' from 职工 order by 奖金 desc

例2：只显示前3条记录

select top 3 姓名，工资，工资*0.08 as '奖金' from 职工 order by 奖金 desc

---

按需求动态排序 case

use db_name
go
select * from tb_name order by case col_name 
    when 'col1' then 1
    when 'col3' then 2
    when 'col4' then 3
    when 'col2' then 4
end