order by cast(right(col,len(col)-patindex('%[0-9]%',col)+1)as int)
上午给别人写的一个,你看着改改用if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(9)) insert [tb] select '和-123' union all select '和-12' union all select '呵呵-1' union all select '和-10' union all select 'aaa-1' union all select 'aaa-10' union all select '呵呵-23'--------------------------------查询开始------------------------------select * from [tb] order by left([col],charindex('-',[col])), cast(stuff([col],1,charindex('-',[col]),'')as int) /* col --------- aaa-1 aaa-10 呵呵-1 呵呵-23 和-10 和-12 和-123(7 行受影响)*/
--> 生成测试数据表: [a] IF OBJECT_ID('[a]') IS NOT NULL DROP TABLE [a] GO CREATE TABLE [a] ([a] [nvarchar](10)) INSERT INTO [a] SELECT 'a1' UNION ALL SELECT 'a2' UNION ALL SELECT 'a10' UNION ALL SELECT 'a11' UNION ALL SELECT 'a12' UNION ALL SELECT 'b2' UNION ALL SELECT 'b12'SELECT * FROM [a] order by left(a,len(a)-len(right(a,patindex('%[^0-9]%',reverse(a))-1))), right(a,patindex('%[^0-9]%',reverse(a))-1)+0-->SQL查询如下: /* a ---------- a1 a2 a10 a11 a12 b2 b12(7 行受影响) */
go
create table [tb]([col] varchar(9))
insert [tb]
select '和-123' union all
select '和-12' union all
select '呵呵-1' union all
select '和-10' union all
select 'aaa-1' union all
select 'aaa-10' union all
select '呵呵-23'--------------------------------查询开始------------------------------select * from [tb]
order by left([col],charindex('-',[col])),
cast(stuff([col],1,charindex('-',[col]),'')as int)
/*
col
---------
aaa-1
aaa-10
呵呵-1
呵呵-23
和-10
和-12
和-123(7 行受影响)*/
IF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a] ([a] [nvarchar](10))
INSERT INTO [a]
SELECT 'a1' UNION ALL
SELECT 'a2' UNION ALL
SELECT 'a10' UNION ALL
SELECT 'a11' UNION ALL
SELECT 'a12' UNION ALL
SELECT 'b2' UNION ALL
SELECT 'b12'SELECT * FROM [a]
order by left(a,len(a)-len(right(a,patindex('%[^0-9]%',reverse(a))-1))),
right(a,patindex('%[^0-9]%',reverse(a))-1)+0-->SQL查询如下:
/*
a
----------
a1
a2
a10
a11
a12
b2
b12(7 行受影响)
*/