1.我建了一张表:sellrecord column name data type allow null
listnumber nchar(16) no2.表内容:
listnumber
13-1
13-10
13-100
13-108
13-11
13-15
13-18
13-2
13-3
14-1
14-2
14-69
3.我想按照'-'左边和'-'右边同时排序,可是输出的结果是根本没有排序!!4.sql语句:
select *
from dbo.SellRecord /*表名*/
order by convert(int,left(ListNumber,charindex('-',ListNumber)--按照'-'左边部分排序
-1) ),convert(int,right(listnumber,(len(listnumber)-charindex('-',ListNumber))))--按照'-'右边排序 5.哪位老师知道其中缘由,还望不吝赐教!!
listnumber nchar(16) no2.表内容:
listnumber
13-1
13-10
13-100
13-108
13-11
13-15
13-18
13-2
13-3
14-1
14-2
14-69
3.我想按照'-'左边和'-'右边同时排序,可是输出的结果是根本没有排序!!4.sql语句:
select *
from dbo.SellRecord /*表名*/
order by convert(int,left(ListNumber,charindex('-',ListNumber)--按照'-'左边部分排序
-1) ),convert(int,right(listnumber,(len(listnumber)-charindex('-',ListNumber))))--按照'-'右边排序 5.哪位老师知道其中缘由,还望不吝赐教!!
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @sellrecord
DECLARE @sellrecord TABLE (listnumber VARCHAR(6))
INSERT INTO @sellrecord
SELECT '13-1' UNION ALL
SELECT '13-10' UNION ALL
SELECT '13-100' UNION ALL
SELECT '13-108' UNION ALL
SELECT '13-11' UNION ALL
SELECT '13-15' UNION ALL
SELECT '13-18' UNION ALL
SELECT '13-2' UNION ALL
SELECT '13-3' UNION ALL
SELECT '14-1' UNION ALL
SELECT '14-2' UNION ALL
SELECT '14-69'--SQL查询如下:select *
from @SellRecord /*表名*/
order by convert(int,left(ListNumber,charindex('-',ListNumber)--按照'-'左边部分排序
-1) ),convert(int,right(listnumber,(len(listnumber)-charindex('-',ListNumber))))/*
listnumber
----------
13-1
13-2
13-3
13-10
13-11
13-15
13-18
13-100
13-108
14-1
14-2
14-69(12 行受影响)
*/
select convert(int,left(ListNumber,charindex('-',ListNumber)-1)),convert(int,right(listnumber,(len(listnumber)-charindex('-',ListNumber))))
from dbo.SellRecord
order by convert(int,left(ListNumber,charindex('-',ListNumber)-1)),convert(int,right(listnumber,(len(listnumber)-charindex('-',ListNumber))))--按照'-'右边排序 /*
----------- -----------
13 0
13 0
13 0
13 0
13 0
13 0
13 0
13 0
13 0
14 0
14 0
14 0(所影响的行数为 12 行)*/
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @sellrecord
DECLARE @sellrecord TABLE (listnumber VARCHAR(6))
INSERT INTO @sellrecord
SELECT '13-1' UNION ALL
SELECT '13-10' UNION ALL
SELECT '13-100' UNION ALL
SELECT '13-108' UNION ALL
SELECT '13-11' UNION ALL
SELECT '13-15' UNION ALL
SELECT '13-18' UNION ALL
SELECT '13-2' UNION ALL
SELECT '13-3' UNION ALL
SELECT '14-1' UNION ALL
SELECT '14-2' UNION ALL
SELECT '14-69'--SQL查询如下:SELECT *
FROM @sellrecord
ORDER BY
CONVERT(INT,LEFT(listnumber,CHARINDEX('-',listnumber)-1)),
CONVERT(INT,RIGHT(listnumber,CHARINDEX('-',REVERSE(listnumber))-1))/*
listnumber
----------
13-1
13-2
13-3
13-10
13-11
13-15
13-18
13-100
13-108
14-1
14-2
14-69(12 行受影响)
*/
go
insert sellrecord select '13-1'
insert sellrecord select '13-10'
insert sellrecord select '13-100'
insert sellrecord select '13-108'
insert sellrecord select '13-11'
insert sellrecord select '13-15'
insert sellrecord select '13-18'
insert sellrecord select '13-2'
insert sellrecord select '13-3'
insert sellrecord select '14-1'
insert sellrecord select '14-2'
insert sellrecord select '14-69'
go
--右边写的不对,nvarchar(16)是一个定长的字符类型,不满16位的以空格填充,所以你要用SUBSTRING函数
--如果用VARCHAR类型,就不会有这个错误了。
select *
from dbo.SellRecord
order by convert(int,left(ListNumber,charindex('-',ListNumber)-1)),convert(int,substring(ListNumber,charindex('-',ListNumber)+1,len(ListNumber)-charindex('-',ListNumber)))/*
listnumber
----------------
13-1
13-2
13-3
13-10
13-11
13-15
13-18
13-100
13-108
14-1
14-2
14-69
(所影响的行数为 12 行)
*/
13-1
13-2
13-3
13-10
13-11
13-15
13-18
13-100
13-108
14-1
14-2
14-69
1.先前是因为我把listnumber 列的数据类型设置为nchar(16),
执行下列语句后:
select *
from SellRecord /*表名*/
order by convert(int,left(ListNumber,charindex('-',ListNumber)--按照'-'左边部分排序
-1) ),convert(int,right(listnumber,(len(listnumber)-charindex('-',ListNumber)))) --->输出结果为:(NG) 13-1
13-10
13-100
13-108
13-11
13-15
13-18
13-2
13-3
14-1
14-2
14-69 2.可是如果把listnumber 列的数据类型设置为varchar(16)类型,
执行下列语句后:
select *
from SellRecord /*表名*/
order by convert(int,left(ListNumber,charindex('-',ListNumber)--按照'-'左边部分排序
-1) ),convert(int,right(listnumber,(len(listnumber)-charindex('-',ListNumber)))) --->输出结果为:(ok) 13-1
13-2
13-3
13-10
13-11
13-15
13-18
13-100
13-108
14-1
14-2
14-693.为什么都是char类型,输出结果确实有这么大的差异??
go
insert sellrecord select '13-1'
insert sellrecord select '13-10'
insert sellrecord select '13-100'
insert sellrecord select '13-108'
insert sellrecord select '13-11'
insert sellrecord select '13-15'
insert sellrecord select '13-18'
insert sellrecord select '13-2'
insert sellrecord select '13-3'
insert sellrecord select '14-1'
insert sellrecord select '14-2'
insert sellrecord select '14-69'
go
--右边写的不对,nvarchar(16)是一个定长的字符类型,不满16位的以空格填充,所以你要用SUBSTRING函数。
--如果用VARCHAR类型的,就不会有这个问题了。
select *
from dbo.SellRecord
order by convert(int,left(ListNumber,charindex('-',ListNumber)-1)),convert(int,substring(ListNumber,charindex('-',ListNumber)+1,len(ListNumber)-charindex('-',ListNumber)))/*
listnumber
----------------
13-1
13-2
13-3
13-10
13-11
13-15
13-18
13-100
13-108
14-1
14-2
14-69
(所影响的行数为 12 行)
*/
drop table SellRecord
--右边写的不对,nchar(16)是一个定长的字符类型,不满16位的以空格填充,所以你要用SUBSTRING函数。
--如果用VARCHAR类型的,就不会有这个问题了。
select listnumber, right(listnumber,(len(listnumber)-charindex('-',ListNumber))) --右边部分
from dbo.SellRecord /*表名*/
order by convert(int,left(ListNumber,charindex('-',ListNumber)
-1) ),convert(int,right(listnumber,(len(listnumber)-charindex('-',ListNumber))))
/*
listnumber
---------------- ----------------
13-1
13-10
13-100
13-108
13-11
13-15
13-18
13-2
13-3
14-1
14-2
14-69 (所影响的行数为 12 行)
*/