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

排序问题,求帮助~~~~
表中一列数据为:7,2,a,e,8,1,c等,为数据和字母混合,
先根据数字顺序排序,再根据字母顺序排序,得到结果:1,2,7,8,a,c,e


------解决方案--------------------
select * from tb order by uncode(字段)
------解决方案--------------------
SQL code

CREATE TABLE #T1
(
    col varchar(10) not null
)
insert into #T1
Select '1' union 
Select '4' union 
Select 'a' union 
Select 'c' union 
Select '7' union 
Select '2' union 
Select 't' union 
Select 'b' 
Select * From #T1 order by col

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

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
    DROP TABLE tba
END
GO
CREATE TABLE tba
(
    memo VARCHAR(100)
)
GO
INSERT INTO tba
SELECT '7' UNION
SELECT '21' UNION
SELECT 'a' UNION
SELECT 'e' UNION
SELECT '8' UNION
SELECT '1' UNION
SELECT 'c'
GO

SELECT memo
FROM tba
ORDER BY CASE WHEN ISNUMERIC(Memo) > 0 THEN CAST(memo AS INT)
              ELSE 2147483647 END 

memo
1
7
8
21
a
c
e