SQL函数应用问题，很苦恼！！

[tu_quan_ren]

1.我建了一张表:sellrecord

column name data type allow null
listnumber nchar(16) no

2.表内容:
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.哪位老师知道其中缘由，还望不吝赐教!!

[liangCK]

你预期的结果是怎样的呢?

[liangCK]

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

[sdhdy]

--右边写的不对       
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 行）

*/

[liangCK]

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

[sdhdy]

create table sellrecord(listnumber nvarchar(16) not null)
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 行）
*/

[tu_quan_ren]

我要的结果就是:
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

２.可是如果把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-69

3.为什么都是char类型，输出结果确实有这么大的差异??

[sdhdy]

create table sellrecord(listnumber nchar(16) not null)
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

[sdhdy]

[Quote=引用 7 楼 sdhdy 的回复:]
SQL codecreate table sellrecord(listnumber nchar(16) not null)
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 …
[/Quote]
注释写错了。

--右边写的不对,nchar(16)是一个定长的字符类型，不满16位的以空格填充，所以你要用SUBSTRING函数。
--如果用VARCHAR类型的，就不会有这个问题了。  

[sdhdy]

--把右边部分SELECT出来，你就明白了，实际上右边那几位都是以空格填充。
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 行）
*/

[tu_quan_ren]

yeah!!我明白了，多谢，多谢！！