27,582
社区成员




----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-16 11:39:32
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([aaa] varchar(5))
insert [tb]
select 'A1-1' union all
select 'A1-2' union all
select 'A2-1' union all
select 'A2-4' union all
select 'A3-1' union all
select 'A20-1' union all
select 'A9-1' union all
select 'A9-3' union all
select 'B1-2' union all
select 'B3-2' union all
select 'B10-2' union all
select 'B9-3'
--------------开始查询--------------------------
select * from [tb] order by charindex('-',aaa)
----------------结果----------------------------
/*aaa
-----
A1-1
A1-2
A2-1
A2-4
A3-1
A9-1
A9-3
B1-2
B3-2
B9-3
B10-2
A20-1
(所影响的行数为 12 行)
*/
if object_id('[tb]') is not null drop table [tb]
create table [tb]([aaa] char(10))
insert [tb]
select 'A1-1' union all
select 'A1-2' union all
select 'A2-1' union all
select 'A9-1' union all
select 'A9-3' union all
select 'A2-4' union all
select 'A3-1' union all
select 'A20-1' union all
select 'B1-2' union all
select 'B3-2' union all
select 'B10-21' union all
select 'B10-9' union all
select 'B9-3'
SELECT *
FROM [tb]
ORDER BY LEFT(aaa,1)
,CAST(SUBSTRING(aaa,2,CHARINDEX('-',aaa) - 2 ) AS INT)
,CAST(SUBSTRING(aaa,CHARINDEX('-',aaa) + 1,LEN(aaa) - CHARINDEX('-',aaa)) AS INT)
if object_id('[tb]') is not null drop table [tb]
create table [tb]([aaa] varchar(5))
insert [tb]
select 'A1-1' union all
select 'A1-2' union all
select 'A2-1' union all
select 'A2-4' union all
select 'A3-1' union all
select 'A20-1' union all
select 'A9-1' union all
select 'A9-3' union all
select 'B1-2' union all
select 'B3-2' union all
select 'B10-2' union all
select 'B9-3'
select * from tb order by substring(aaa,1,1),cast(substring(aaa,2,charindex('-',aaa)-2) as int)
----
(12 行受影响)
aaa
-----
A1-1
A1-2
A2-1
A2-4
A3-1
A9-1
A9-3
A20-1
B1-2
B3-2
B9-3
B10-2
(12 行受影响)
-----
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================
if object_id('[tb]') is not null
drop table [tb]
go
create table [tb]([aaa] varchar(5))
insert [tb]
select 'A1-1' union all
select 'A1-2' union all
select 'A2-1' union all
select 'A2-4' union all
select 'A3-1' union all
select 'A9-1' union all
select 'A20-1' union all
select 'A9-3' union all
select 'B1-2' union all
select 'B3-2' union all
select 'B10-2' union all
select 'B9-3'
go
select aaa from tb
order by left(aaa,1),RIGHT(aaa,LEN(aaa)-CHARINDEX('-',aaa)),
case when substring(aaa,2,CHARINDEX('-',aaa)-2)<10 then '0'+substring(aaa,2,CHARINDEX('-',aaa)-2) else substring(aaa,2,CHARINDEX('-',aaa)-2) end
/*------------
(
(12 行受影响)
aaa
-----
A1-1
A2-1
A3-1
A9-1
A20-1
A1-2
A9-3
A2-4
B1-2
B3-2
B10-2
B9-3
(12 行受影响)
-------*/
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================
if object_id('[tb]') is not null
drop table [tb]
go
create table [tb]([aaa] varchar(5))
insert [tb]
select 'A1-1' union all
select 'A1-2' union all
select 'A2-1' union all
select 'A2-4' union all
select 'A3-1' union all
select 'A9-1' union all
select 'A8-1' union all
select 'A9-3' union all
select 'B1-2' union all
select 'B3-2' union all
select 'B7-2' union all
select 'B9-3'
go
select aaa from tb
order by left(aaa,1),RIGHT(aaa,LEN(aaa)-CHARINDEX('-',aaa)),substring(aaa,2,CHARINDEX('-',aaa)-2)
/*------------
(12 行受影响)
aaa
-----
A1-1
A2-1
A3-1
A8-1
A9-1
A1-2
A9-3
A2-4
B1-2
B3-2
B7-2
B9-3
-------*/
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([aaa] varchar(5))
insert [tb]
select 'A1-1' union all
select 'A1-2' union all
select 'A2-1' union all
select 'A2-4' union all
select 'A3-1' union all
select 'A20-1' union all
select 'A9-1' union all
select 'A9-3' union all
select 'B1-2' union all
select 'B3-2' union all
select 'B10-2' union all
select 'B9-3'
select * from tb order by substring(aaa,1,1) asc, charindex('-',aaa) asc
/*
A1-1
A1-2
A2-1
A2-4
A3-1
A9-1
A9-3
A20-1
B1-2
B3-2
B9-3
B10-2
*/