有一表,表名sellRecord 字段名:listNumber
字段值为: 13-1,13-2,13-3,13-10,13-100,13-108,13-18,13-11,13-15,14-1,14-2
要对求对段排序
输出为:
13-1,13-2,13-3,13-10,13-11,13-15,13-18,13-100,13-108,14-1,14-2
思路: 查找字段中‘-'所处的位置,分别获取'-'左侧和右侧的值,进行排序语句如下:select listnumber from sellrecord
order by
convert(int ,left(listNumber, charindex('-',listNumber)-1)),
convert(int, right(listNumber,len(listNumber)-charindex('-',listNumber))) 发现无效,将语句更改为:select listnumber from sellrecord
order by
convert(int ,left(listNumber, charindex('-',listNumber)-1)),
convert(int,stuff(listNumber,1, charindex('-',listNumber),''))这样就可以了convert(int, right(listNumber,len(listNumber)-charindex('-',listNumber)))
convert(int,stuff(listNumber,1, charindex('-',listNumber),''))
这两条语句实现效果应该是一样的呀,为啥查询结果不同呢? 求解~
字段值为: 13-1,13-2,13-3,13-10,13-100,13-108,13-18,13-11,13-15,14-1,14-2
要对求对段排序
输出为:
13-1,13-2,13-3,13-10,13-11,13-15,13-18,13-100,13-108,14-1,14-2
思路: 查找字段中‘-'所处的位置,分别获取'-'左侧和右侧的值,进行排序语句如下:select listnumber from sellrecord
order by
convert(int ,left(listNumber, charindex('-',listNumber)-1)),
convert(int, right(listNumber,len(listNumber)-charindex('-',listNumber))) 发现无效,将语句更改为:select listnumber from sellrecord
order by
convert(int ,left(listNumber, charindex('-',listNumber)-1)),
convert(int,stuff(listNumber,1, charindex('-',listNumber),''))这样就可以了convert(int, right(listNumber,len(listNumber)-charindex('-',listNumber)))
convert(int,stuff(listNumber,1, charindex('-',listNumber),''))
这两条语句实现效果应该是一样的呀,为啥查询结果不同呢? 求解~
ORDER BY CONVERT(INT,STUFF(listNumber,1,CHARINDEX('-',listNumber),''))
convert(int, right(listNumber,len(listNumber)-charindex('_',listNumber)))
convert(int,stuff(listNumber,1, charindex('_',listNumber),''))
执行结果就是不一样呢select listnumber from sellrecord
order by
convert(int ,left(listNumber, charindex('-',listNumber)-1)),
convert(int, right(listNumber,len(listNumber)-charindex('-',listNumber))) 执行结果为
13-1
13-2
13-3
13-10
13-100
13-108
13-18
13-11
13-15
14-1
14-2 select listnumber from sellrecord
order by
convert(int ,left(listNumber, charindex('-',listNumber)-1)),
/*convert(int, right(listNumber,len(listNumber)-charindex('-',listNumber))) */
convert(int,stuff(listNumber,1, charindex('-',listNumber),''))
执行结果为:
13-1
13-2
13-3
13-10
13-11
13-15
13-18
13-100
13-108
14-1
14-2 数据库是 sql 2005 两语句除了写法不同,真不知道区别在哪里了。
create table sellRecord(listNumber nvarchar(10))
insert into sellRecord select '13-1' union all select '13-2' union all select '13-3'
union all select '13-10' union all select '13-100' union all select '13-108' union all select '13-18'
union all select '13-11' union all select '13-15' union all select '14-1' union all select '14-2'
go
select listnumber from sellrecord
order by
convert(int ,left(listNumber, charindex('-',listNumber)-1)),
convert(int, right(listNumber,len(listNumber)-charindex('-',listNumber)))
go
drop table sellRecord
/*
listnumber
----------
13-1
13-2
13-3
13-10
13-11
13-15
13-18
13-100
13-108
14-1
14-2(11 行受影响)
*/
INSERT INTO sellRecord
SELECT '13-1' UNION ALL
SELECT '13-2' UNION ALL
SELECT '13-3' UNION ALL
SELECT '14-1' UNION ALL
SELECT '13-100' UNION ALL
SELECT '13-108' UNION ALL
SELECT '14-2' UNION ALL
SELECT '13-11' UNION ALL
SELECT '13-15' UNION ALL
SELECT '13-10' UNION ALL
SELECT '13-18'SELECT * FROM sellRecord
ORDER BY
CAST(LEFT(listNumber,CHARINDEX('-',listNumber)-1) AS INT),
CAST(RIGHT(listNumber,LEN(listNumber)-CHARINDEX('-',listNumber)) AS INT)
--结果
13-1
13-2
13-3
13-10
13-11
13-15
13-18
13-100
13-108
14-1
14-2没问题哇~。。