DECLARE @tb table(ID varchar(20)) insert into @tb select '111111' ID union all select '123456' union all select '4567' union all select '5678' union all select 'aaaaaa' union all select 'A-1' union all select 'B-3' union all select 'C-2' union all select 'CS-1' union all select 'CS-11' union all select 'CS-2'select * from @tb order by case when isnumeric(ID)=1 then substring(ID,0,1) else len(ID) end/* ID -------------------- 111111 123456 4567 5678 A-1 B-3 C-2 CS-1 CS-2 CS-11 aaaaaa(11 行受影响) */
呵呵,能不能说得清楚点啊,order by
给你举个例子。你参照着做(此种情况仅适用于结果集是可预测的情况下) select * from table order by case ID when '111111' then 'A' when '123456' then 'B' when '4567' then 'C' when '5678' then 'D' when 'A-1' then 'E' …… end 也就是说将特定的结果看成特定的值,然后再进行排序 上面这个方法使用与结果集有限的情况下 在结果集数量不可预料的情况下,通过相关算法对其赋值参与排序。即可达到同样的效果
如果你的排序有一定的规律。将你的规律嵌在order by 语句之后即可
DECLARE @tb table(ID varchar(20)) insert into @tb select '111111' ID union all select '123456' union all select '4567' union all select '5678' union all select 'aaaaaa' union all select 'A-1' union all select 'B-3' union all select 'C-2' union all select 'CS-1' union all select 'CS-11' union all select 'CS-2' SELECT * FROM @TB ORDER BY ASCII(LEFT(ID,1)),ID (所影响的行数为 11 行)ID -------------------- 111111 123456 4567 5678 A-1 B-3 C-2 CS-1 CS-11 CS-2 aaaaaa(所影响的行数为 11 行)
说清楚你排序的具体规则, 下面排序的规则是:是数字的就按第一个数字大小升序,否则就按字符长度升序DECLARE @tb table(ID varchar(20)) insert into @tb select 'CS-11' union all select '4567' union all select '5678' union all select 'aaaaaa' union all select '111111' ID union all select 'A-1' union all select '123456' union all select 'B-3' union all select 'C-2' union all select 'CS-1' union all select 'CS-2'select * from @tb order by case when isnumeric(ID)=1 then ASCII((substring(ID,1,1))) else ASCII((substring(ID,1,1)))+len(ID) end/* ID -------------------- 111111 123456 4567 5678 A-1 B-3 C-2 CS-1 CS-2 CS-11 aaaaaa(11 行受影响)*/
没什么规律性
DECLARE @tb table(ID varchar(20))
insert into @tb
select '111111' ID union all
select '123456' union all
select '4567' union all
select '5678' union all
select 'aaaaaa' union all
select 'A-1' union all
select 'B-3' union all
select 'C-2' union all
select 'CS-1' union all
select 'CS-11' union all
select 'CS-2'select * from @tb order by case when isnumeric(ID)=1 then substring(ID,0,1) else len(ID) end/*
ID
--------------------
111111
123456
4567
5678
A-1
B-3
C-2
CS-1
CS-2
CS-11
aaaaaa(11 行受影响)
*/
select * from table
order by case ID
when '111111' then 'A'
when '123456' then 'B'
when '4567' then 'C'
when '5678' then 'D'
when 'A-1' then 'E'
……
end
也就是说将特定的结果看成特定的值,然后再进行排序
上面这个方法使用与结果集有限的情况下
在结果集数量不可预料的情况下,通过相关算法对其赋值参与排序。即可达到同样的效果
insert into @tb
select '111111' ID union all
select '123456' union all
select '4567' union all
select '5678' union all
select 'aaaaaa' union all
select 'A-1' union all
select 'B-3' union all
select 'C-2' union all
select 'CS-1' union all
select 'CS-11' union all
select 'CS-2'
SELECT * FROM @TB ORDER BY ASCII(LEFT(ID,1)),ID
(所影响的行数为 11 行)ID
--------------------
111111
123456
4567
5678
A-1
B-3
C-2
CS-1
CS-11
CS-2
aaaaaa(所影响的行数为 11 行)
5789
4567
12345
11111
A-1
B-3
C-2
CS-1
CS-2
CS-11
aaaaaa
ffffff
下面排序的规则是:是数字的就按第一个数字大小升序,否则就按字符长度升序DECLARE @tb table(ID varchar(20))
insert into @tb
select 'CS-11' union all
select '4567' union all
select '5678' union all
select 'aaaaaa' union all
select '111111' ID union all
select 'A-1' union all
select '123456' union all
select 'B-3' union all
select 'C-2' union all
select 'CS-1' union all
select 'CS-2'select * from @tb order by case when isnumeric(ID)=1 then ASCII((substring(ID,1,1))) else ASCII((substring(ID,1,1)))+len(ID) end/*
ID
--------------------
111111
123456
4567
5678
A-1
B-3
C-2
CS-1
CS-2
CS-11
aaaaaa(11 行受影响)*/
id 排序因为大写字母的 ascii编码小 所以排前面