MySQL 在 case 语句中保留类型

Question

我有一个根据某些输入而变化的字段:

SELECT 
    CASE input
      WHEN 1 THEN id
      WHEN 2 THEN created_at
      WHEN 3 THEN some_varchar_field
    END as test
FROM ...
ORDER BY 1 ASC;

如果我要按此字段排序，则任何数字字段都未正确排序，我注意到只要存在 VARCHAR/DATETIME 字段，排序依据就不会保留所选字段的类型它将返回该类型，而不是保留列本身的类型。我只是想知道是否有办法保留所选记录的类型。

编辑:将字段转换为 int CAST(id as INT) 不起作用将 case 语句按 order by 排列也不起作用。

Answer 1

我认为这个解决方案不太漂亮，但应该可行。

<强> SQL Fiddle Demo

为每个字段创建一个新列，并注明其顺序。

SELECT CASE 1
          WHEN 1 THEN id 
          WHEN 2 THEN created_at
          WHEN 3 THEN scomment
       END as field_name 
FROM  (       
      SELECT I.id, I.rid, 
             C.created_at, C.rcreated_at,
             S.scomment, S.rscomment       
      FROM (
            SELECT t.id, 
                   @rowid := @rowid + 1 AS rid
            FROM test t, 
                   (SELECT @rowid := 0) r
            ORDER BY t.id     
           ) I   -- add sort column for id
      JOIN (     
            SELECT t.id, created_at,
                   @rowcreated_at := @rowcreated_at + 1 AS rcreated_at
            FROM test t, 
                   (SELECT @rowcreated_at := 0) r
            ORDER BY t.created_at    
           ) C  -- add sort column for created_at
        ON I.id = C.id
      JOIN (  
            SELECT t.id, scomment,
                   @rowscomment := @rowscomment + 1 AS rscomment
            FROM test t, 
                   (SELECT @rowscomment := 0) r
            ORDER BY t.scomment   
           ) S  -- add sort column for scomment
         ON I.id = S.id
     ) T
ORDER BY CASE 1
          WHEN 1 THEN rid 
          WHEN 2 THEN rcreated_at
          WHEN 3 THEN rscomment
       END