先创建一个表
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
乒乓球
轮滑
排球
放放风方法
打散
试验下行不行?
我靠!
真不行?
行的嘛!