create table tempabc (a varchar(50)) insert into tempabc select '1-1-1' union all select '1-3-2' union all select '1-2-2' union all select '1-1-2' union all select '1-3-1' union all select '1-2-1' union all select '1-1-9'select * from tempabc order by adrop table tempabc (所影响的行数为 7 行)a -------------------------------------------------- 1-1-1 1-1-2 1-1-9 1-2-1 1-2-2 1-3-1 1-3-2(所影响的行数为 7 行)
--如果只是这样,可直接排序create table tb(a varchar(10)) insert into tb values('1-1-1') insert into tb values('1-1-2') insert into tb values('1-2-1') insert into tb values('1-2-2') goselect * from tb order by adrop table tb/* a ---------- 1-1-1 1-1-2 1-2-1 1-2-2(所影响的行数为 4 行) */
--如果位数不定.create table tb(a varchar(10)) insert into tb values('1-11-1') insert into tb values('1-1-2') insert into tb values('1-21-1') insert into tb values('1-20-2') goselect cast(left(a,charindex('-',a) -1) as int) a1 , cast(substring(a,charindex('-',a) + 1 , charindex('-',a,charindex('-',a)+1) - charindex('-',a) -1) as int) a2,cast(left(reverse(a),charindex('-',reverse(a))-1) as int) a3 from tb order by a1,a2,a3 drop table tb/* a1 a2 a3 ----------- ----------- ----------- 1 1 2 1 11 1 1 20 2 1 21 1(所影响的行数为 4 行) */
--确保有'-'号.create table tb(a varchar(10)) insert into tb values('1-11-1') insert into tb values('1-1-2') insert into tb values('1-21-1') insert into tb values('1-20-2') goselect cast(left(a,charindex('-',a) -1) as int) a1 , cast(substring(a,charindex('-',a) + 1 , charindex('-',a,charindex('-',a)+1) - charindex('-',a) -1) as int) a2,cast(left(reverse(a),charindex('-',reverse(a))-1) as int) a3 from tb where charindex('-',a)>0 order by a1,a2,a3 drop table tb/* a1 a2 a3 ----------- ----------- ----------- 1 1 2 1 11 1 1 20 2 1 21 1(所影响的行数为 4 行) */
--直接按字段升序排序即可select * from 表名 order by 字段名
最好把你的要求写明白 如果只是这些的话 使用 select * from table order by 列名 是最简单的
insert into tempabc select '1-1-1'
union all select '1-3-2'
union all select '1-2-2'
union all select '1-1-2'
union all select '1-3-1'
union all select '1-2-1'
union all select '1-1-9'select * from tempabc order by adrop table tempabc
(所影响的行数为 7 行)a
--------------------------------------------------
1-1-1
1-1-2
1-1-9
1-2-1
1-2-2
1-3-1
1-3-2(所影响的行数为 7 行)
insert into tb values('1-1-1')
insert into tb values('1-1-2')
insert into tb values('1-2-1')
insert into tb values('1-2-2')
goselect * from tb order by adrop table tb/*
a
----------
1-1-1
1-1-2
1-2-1
1-2-2(所影响的行数为 4 行)
*/
insert into tb values('1-11-1')
insert into tb values('1-1-2')
insert into tb values('1-21-1')
insert into tb values('1-20-2')
goselect cast(left(a,charindex('-',a) -1) as int) a1 , cast(substring(a,charindex('-',a) + 1 , charindex('-',a,charindex('-',a)+1) - charindex('-',a) -1) as int) a2,cast(left(reverse(a),charindex('-',reverse(a))-1) as int) a3
from tb
order by a1,a2,a3
drop table tb/*
a1 a2 a3
----------- ----------- -----------
1 1 2
1 11 1
1 20 2
1 21 1(所影响的行数为 4 行)
*/
insert into tb values('1-11-1')
insert into tb values('1-1-2')
insert into tb values('1-21-1')
insert into tb values('1-20-2')
goselect cast(left(a,charindex('-',a) -1) as int) a1 , cast(substring(a,charindex('-',a) + 1 , charindex('-',a,charindex('-',a)+1) - charindex('-',a) -1) as int) a2,cast(left(reverse(a),charindex('-',reverse(a))-1) as int) a3
from tb
where charindex('-',a)>0
order by a1,a2,a3
drop table tb/*
a1 a2 a3
----------- ----------- -----------
1 1 2
1 11 1
1 20 2
1 21 1(所影响的行数为 4 行)
*/
from 表名
order by 字段名
如果只是这些的话
使用
select * from table order by 列名
是最简单的