SQL的排序问题

netcup 2009-07-16 11:34:32

表中字段JWH是CHAR(10)
JWH内容
A1-1
A1-2
A2-1
A2-4
A3-1
A20-1
A9-1
A9-3
B1-2
B3-2
B10-2
B9-3
等等
为什么 ORDER BY JWH 后，结果是下面的，注意9的排序
A1-1
A1-2
A20-1
A2-1
A2-4
A3-1
A9-1
A9-3
B1-2
B10-2
B3-1
B9-3

似乎只排序到A1这两位数字上。A10就在A2前面，9反而成了最后了

...全文

140 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

--小F-- 2009-07-16

打赏
举报

----------------------------------------------------------------

-- 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 行）

*/

华夏小卒 2009-07-16

打赏
举报

先按照ABCD...
再按1234.。。

xsm545 2009-07-16

打赏
举报

顶..都是高人啊

xiaoliaoyun 2009-07-16

打赏
举报



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)

linguojin11 2009-07-16

打赏
举报

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 行受影响)

-----

feixianxxx 2009-07-16

打赏
举报

-- =========================================

-- -----------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 行受影响)





-------*/

feixianxxx 2009-07-16

打赏
举报

-- =========================================

-- -----------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

-------*/

feixianxxx 2009-07-16

打赏
举报

[Quote=引用 3 楼 netcup 的回复:]
我需要按A1-1,A2-1......A9-1,A10-1...A20-1这样正常排序排列下去，如何ORDER by呢？
[/Quote]
那B1-1是不是排在A9-3
后面阿

zc_0101 2009-07-16

打赏
举报



--> 测试数据：[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

*/