日期:2014-05-18  浏览次数:20413 次

很基础的排序问题
建表:
SQL code
if object_id('[#T]') is not null drop table [#T]
go
CREATE TABLE #T
(
    F1 VARCHAR(20),
    F2 VARCHAR(20),
    F3 VARCHAR(20),
    F4 VARCHAR(20)
)
insert into #T
Select '645',    'BHC04304Y',    '2K-3K5',    'A122'  union
Select '708',    'CHS02232A',    '3K5-5K',    'A4'  union
Select '626',    'CHS02319A',    '4K-6K',    'A5' union
Select '630',    'CHS02324Y',    '4K-6K5',    'A6'  union
Select '653',    'CHS02329A',    '3K5-5K5',    'A123' 

期望的排序结果(依据F4排):
SQL code

708    CHS02232A    3K5-5K    A4
626    CHS02319A    4K-6K    A5
630    CHS02324Y    4K-6K5    A6
645    BHC04304Y    2K-3K5    A122
653    CHS02329A    3K5-5K5    A123

很简单吧

------解决方案--------------------
SQL code

select * from #T
order by STUFF(F4,1,1,'')+1
/*
F1                   F2                   F3                   F4
-------------------- -------------------- -------------------- --------------------
708                  CHS02232A            3K5-5K               A4
626                  CHS02319A            4K-6K                A5
630                  CHS02324Y            4K-6K5               A6
645                  BHC04304Y            2K-3K5               A122
653                  CHS02329A            3K5-5K5              A123
(5 row(s) affected)
*/

------解决方案--------------------
SQL code
select * from tb
order by cast(substring(f4,2,1000) as int)