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反而成了最后了
...全文
83 10 打赏 收藏 转发到动态 举报
AI 作业
写回复
用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
*/
netcup 2009-07-16
  • 打赏
  • 举报
回复
我需要按A1-1,A2-1......A9-1,A10-1...A20-1这样正常排序排列下去,如何ORDER by呢?

27,582

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧