sql server order by 的一些高级用法

先创建一个表

create table ai(
id int not null,
no varchar(10) not null
)
go

往表中插入数据

insert into ai
select 105,’2’
union all
select 105,’1’
union all
select 103,’1’
union all
select 105,’4’
go

查询效果如下

select * from ai 
go

id	no
105	2
105	1
103	1
105	4

一：查询结果如下的解决方案

即要求no列的数据按’4’,’1’,’2’排列

id	no
105	4
105	1
103	1
105	2

解决方案1

//利用函数CHARINDEX 
select * from ai order by charindex(no,'4,1,2')

解决方案2,并且每组再按照id降序排列

//利用函数case 
select * from ai 
    order by 
         case 
         when no='4' then 1 
         when no='1' then 2 
         when no='2' then 3 
         end,
    id desc

解决方案3

//利用UNION 运算符 
select * from ai 
 where no='4' 
union all 
select * from ai 
 where no='1' 
union all 
select * from ai 
 where no='2'

二：查询要求指定no=’4’排第一行，其他的行随机排序

id	no
105	4
105	2
105	1
103	1

解决方案

select * from ai 
 order by 
     case when no='4' then 1 
     else 1+rand() 
     end

三：查询要求所有行随机排序

解决方案

select * from ai   order by newid()

四：有一表ab有列i，其中数据如下：

i	varchar(10)
a	1
a	10
a	101
a	5
p	4
p	41
p	5

现在要求列i中数据先按字母排序，再按数字排序，效果如下

i	varchar(10)
a	1
a	5
a	10
a	101
p	4
p	5
p	41

解决方案

select * from ab   order by left(i,1),convert(int,substring(i,2,8000))

---

SQL Server中使用order by charindex按指定顺序排序

本文导读：在使用SQL Server数据库编程中，有时需要对取出来的数据按照指定的顺序排序，例如，取出来的数据某个字段值有张三、李四、王五，但是要求获取的数据顺序为：李四、张三、王五。我们可以使用order by结合charindex函数实现这个功能。

SqlServer的CHARINDEX方法可以实现对字段按照指定的顺序排序，因此，可以灵活的利用CHARINDEX方法把查询的结果按照我们想要的顺序显示。

实例：在一个名为Product的表中对自增字段ID做in查询，希望查出来的记录ID字段按照12490,12494,12486这个顺序来排

1、得不到正确结果的写法

select * from Product where ID in (12490,12494,12486)

2、使用CHARINDEX函数实现

select * from Product where ID in(12490,12494,12486)   
order by CHARINDEX(','+CONVERT(varchar(10),ID)+',',',12490,12494,12486,') 

实现原理

把ID转成字符串，然后在两边加上","，组合一个字符串，用逗号隔开每个ID，然后查询ID的下标值（之所以两边加上","是因为查询下标的时候类似ID=123和ID=1234会查到同一个下标）

ORDER BY CHARINDEX 语法

根据检索条件把检索结果排序。

1、order by中包含的条件在其他数据之前：

    select * from 表名
    where 列名 in ('条件1','条件2','条件3','条件4')
    order by charindex(列名, '条件1,条件2,条件3')

2、order by中包含的条件在其他数据之后：

    select * from 表名
    where 列名 in ('条件1','条件2','条件3','条件4')
    order by 
  --case when charindex(','+列名+',' ,','+'条件1,条件2,条件3,'+',')>=1 then 0 else 1 end
    charindex(列名, '条件1,条件2,条件3') desc

3、事例

错误sql

select * from table1 where id in (183,200,211,145,89,139,83,45) order by charindex(ltrim(id),'183,200,211,145,89,139,83,45')

每次查询出来的结果都变成了183,83,200,211,145,45,89,139 后两位相同的总是显示到一起去了,不知道为什么,请各位高手帮忙看看,数据可能会上万条.

正确sql

    select * from table1 where id in (183,200,211,145,89,139,83,45) order by
    charindex(','+ltrim(id)+',',',183,200,211,145,89,139,83,45,')

前后加逗号限定

CHARINDEX

返回字符串中指定表达式的起始位置（第一个位置）。

183,200,211,145,89,139,83,45

183 --> 1

83 --> 2：不会去找倒数第二个数83

强制找’,83,‘而不是’83’，

4、order by charindex(bd_value ,'乒乓球,轮滑,排球,放放风方法,打散')

现在想把bd_value 中 乒乓球,轮滑,排球,放放风方法,打散以外的排在他们后面怎么弄？？？默认排到前面去了。

解决办法：

    DECLARE @TABLE TABLE(bd_value  VARCHAR(100))
    
    INSERT INTO @TABLE
    SELECT '乒乓球'
    UNION ALL 
    SELECT '轮滑'
    UNION ALL
    SELECT '排球'
    UNION ALL
    SELECT '放放风方法'
    UNION ALL
    SELECT '打散'
    UNION ALL
    SELECT '试验下行不行？'
    UNION ALL
    SELECT '我靠！'
    UNION ALL
    SELECT '真不行？'
    UNION ALL
    SELECT '行的嘛！'

    SELECT * FROM  @TABLE
    order by 
    case when charindex(','+bd_value+',' ,','+'乒乓球,轮滑,排球,放放风方法,打散'+',')>=1 then 0 else 1 end,
    charindex(','+bd_value+',' ,','+'乒乓球,轮滑,排球,放放风方法,打散'+',')

     
    --------------结果
    bd_value
    乒乓球
    轮滑
    排球
    放放风方法
    打散
    试验下行不行？
    我靠！
    真不行？
    行的嘛！