sql server order by 的一些高级用法

清疚 2021-07-04 12:03 763阅读 0赞

先创建一个表

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

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

即要求no列的数据按’4’,’1’,’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’`排第一行，其他的行随机排序 ###

解决方案

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

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

解决方案

select * from ai   order by newid()

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

<table> 
 <thead> 
  <tr> 
   <th>i</th> 
   <th>varchar(10)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>a</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>a</td> 
   <td>10</td> 
  </tr> 
  <tr> 
   <td>a</td> 
   <td>101</td> 
  </tr> 
  <tr> 
   <td>a</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>p</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>p</td> 
   <td>41</td> 
  </tr> 
  <tr> 
   <td>p</td> 
   <td>5</td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <thead> 
  <tr> 
   <th>i</th> 
   <th>varchar(10)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>a</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>a</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>a</td> 
   <td>10</td> 
  </tr> 
  <tr> 
   <td>a</td> 
   <td>101</td> 
  </tr> 
  <tr> 
   <td>p</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>p</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>p</td> 
   <td>41</td> 
  </tr> 
 </tbody> 
</table>

解决方案

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
        乒乓球
        轮滑
        排球
        放放风方法
        打散
        试验下行不行？
        我靠！
        真不行？
        行的嘛！