排序的问题

Question

AnalysisCode表中有如下字段，其中 CodeID为varchar，保存数据字符或者是英文字母（同一个TypeID只会是数字或字母，不会两样都用），现在我要在select时按[TypeID],[CodeID]排序，也就是select * from AnalysisCode order by [TypeID],[CodeID]。但在排序数字字符时就不是按我想的1，2，3，4....那样排序了，估我把select改为 select * from AnalysisCode order by [TypeID],cast([CodeID] as int。但是遇到字母字符时又会报错，不能使用cast了。那么，我想在同一TypeID下，若CodeID是数字，那么就按cast([CodeID] as int方式排序，若CodeID是字母，那就直接按字母排序，如何才能做到？TypeID（int型）  CodeID（varchar型）  CodeName （varchar型）
  1                   1                  .....
  1                   2                  .....
  ..                  ..                 .....
  1                   10                 .....
  1                   11                 .....
  2                   A                  .....
  2                   B                  .....
  2                   H                  .....

Answer 1

CodeID先按数字从小到大排.然后再按字母从a-z排?

Answer 2

order by case when patindex('%[a-z]%',TypeID)>0 then TypeID else cast([CodeID] as int end

Answer 3

或者不这样排order by ASCII(left(TypeID,1))
这样就是先按数字拍，然后按字母拍，仅限第一个字符

Answer 4

还是报错，case能用在order中吗？

Answer 5

仅排第一个字符的话直接ORDER BY CodeID 就可以

Answer 6

一个笨办法。用union all，先把数字的抽出来排了，再union all 字母排的

Answer 7

我是CodeID排序问题，不是TypeID，我使用如下还是报错order by [TypeID],
case 
  when patindex('[A-Z]',[CodeID])>0 then [CodeID] 
  else cast([CodeID] as int) 
end

Answer 8

--> 生成测试数据: @tb
DECLARE @tb TABLE (TypeID int,CodeID varchar(10),CodeName varchar(5))
INSERT INTO @tb
SELECT 1,'1','.....' UNION ALL
SELECT 1,'2','.....' UNION ALL
SELECT 1,'9','.....' UNION ALL
SELECT 1,'A','.....' UNION ALL
SELECT 1,'30','....' UNION ALL
SELECT 1,'4B','....' UNION ALL
SELECT 1,'BD','....' UNION ALL
SELECT 1,'ZIEF','....' UNION ALL
SELECT 1,'0ADFEF','....' UNION ALL
SELECT 1,'ADFEF','....' UNION ALL
SELECT 1,'B5','....' UNION ALL
SELECT 1,'10','.....' UNION ALL
SELECT 1,'11','.....' UNION ALL
SELECT 2,'A','.....' UNION ALL
SELECT 2,'B','.....' UNION ALL
SELECT 2,'H','.....'--SQL查询如下:SELECT * FROM @tb
ORDER BY 
    TypeID,
    CASE WHEN ISNUMERIC(CodeID)=1 THEN 0 ELSE 1 END,
    CASE WHEN ISNUMERIC(CodeID)=1 THEN 
              RIGHT(REPLICATE('0',(SELECT MAX(LEN(CodeID)) FROM @tb))+CodeID,
                                  (SELECT MAX(LEN(CodeID)) FROM @tb))
         ELSE CodeID END/*
TypeID      CodeID     CodeName
----------- ---------- --------
1           1          .....
1           2          .....
1           9          .....
1           10         .....
1           11         .....
1           30         ....
1           0ADFEF     ....
1           4B         ....
1           A          .....
1           ADFEF      ....
1           B5         ....
1           BD         ....
1           ZIEF       ....
2           A          .....
2           B          .....
2           H          .....(16 行受影响)*/

Answer 9

declare @t table (typeid int,codeid varchar(10))
insert into @t select 1,'1'
     union all select 2,'A'
     union all select 1,'2'
     union all select 2,'B'
     union all select 2,'H'
     union all select 1,'11'
     union all select 1,'10'
select * from @t
   order by typeid,case when isnumeric(codeid)=1 then cast(codeid as int) end,
            case when isnumeric(codeid)<>1 then codeid end/*
typeid      codeid
----------- ----------
1           1
1           2
1           10
1           11
2           A
2           B
2           H(7 行受影响)
*/

Answer 10

是不是在先排typeID的基础之上再排codeID？
我读了半天你的描述猜的你的意思

Answer 11

先将数字型的CodeID转为Int保存在一个临时的字段Number里（字符型CodeID,其对应的Number都设为0），然后就可以按照TypeID, Number, CodeID的顺序排序了。代码如下：select TypeID, CodeID, CodeName
from
(
select *, case ISNUMERIC(CodeID) when 1 then cast(CodeID as int) else 0 end as Number from AnalysisCode 
) a
order by TypeID, Number, CodeID

Answer 12

order by typeid,case when isnumeric(codeid)=1 then cast(codeid as int) end,
            case when isnumeric(codeid)<>1 then codeid end以上的确可行，但为何改为如下就报错呢？order by typeid,case when isnumeric(codeid)=1 then cast(codeid as int) else codeid  end

Answer 13

因为cast(codeid as int)是int型，而codeid是varchar型，同一字段不能有两种类型。

Answer 14

DECLARE @tb TABLE (TypeID int,CodeID varchar(10),CodeName varchar(5))
INSERT INTO @tb
SELECT 1,'1','.....' UNION ALL
SELECT 1,'2','.....' UNION ALL
SELECT 1,'9','.....' UNION ALL
SELECT 1,'A','.....' UNION ALL
SELECT 1,'30','....' UNION ALL
SELECT 1,'4B','....' UNION ALL
SELECT 1,'BD','....' UNION ALL
SELECT 1,'ZIEF','....' UNION ALL
SELECT 1,'0ADFEF','....' UNION ALL
SELECT 1,'ADFEF','....' UNION ALL
SELECT 1,'B5','....' UNION ALL
SELECT 1,'10','.....' UNION ALL
SELECT 1,'11','.....' UNION ALL
SELECT 2,'A','.....' UNION ALL
SELECT 2,'B','.....' UNION ALL
SELECT 2,'H','.....'
SELECT * FROM @TB
ORDER BY TypeId ASC,
(CASE WHEN ISNUMERIC(CodeId)=1 THEN 1 ELSE 0 END) DESC,
(CASE WHEN ISNUMERIC(CodeId)=1 THEN CAST(CodeId AS INT) ELSE NULL END) ASC,
(CASE WHEN ISNUMERIC(CodeId)<>1 THEN  CodeId ELSE NULL END) ASC
(16 row(s) affected)
TypeID      CodeID     CodeName
----------- ---------- --------
1           1          .....
1           2          .....
1           9          .....
1           10         .....
1           11         .....
1           30         ....
1           0ADFEF     ....
1           4B         ....
1           A          .....
1           ADFEF      ....
1           B5         ....
1           BD         ....
1           ZIEF       ....
2           A          .....
2           B          .....
2           H          .....(16 row(s) affected)感谢vivian_lanlan提供测试数据代码