MYSQL在两个特定记录和数据之间排序NULL

Question

有没有办法将特定记录中MYSQL使用的默认顺序设置为NULL。

我的专栏:

• id:id
• 状态:“正在下载”、“错误”、“完成”、“失败”
• try:下载文件的尝试次数。

结果顺序:

• 正在下载
• 错误:如果尝试在 0 到 20 之间
• 空
• 错误:尝试大于 20
• 失败

我的 table :

id       state          try
----------------------------
1        NULL            0
2        error           25
3        downloading     1
4        NULL            0
5        finish          2
6        error           3
7        NULL            0
8        downloading     5
9        fail            51
10       error           3
11       error           21
12       error           8
13       finish          10
14       downloading     6
15       error           17

运行查询后的结果:

id       state         try
-------------------------------
3        downloading     1
8        downloading     5
14       downloading     6
10       error           3
12       error           8
15       error           17
6        error           3
1        NULL            0
4        NULL            0
7        NULL            0
11       error           21
2        error           25
9        fail            51

我的查询不起作用:

 SELECT * FROM `report_table`
            WHERE state IN ('downloading', 'error','fail')
                OR state IS NULL
            ORDER BY
                CASE
                    WHEN state = 'downloading' THEN 1
                    WHEN state IS NULL THEN 2
                    WHEN state = 'error' THEN 3
                    WHEN state = 'fail' THEN 4
                    ELSE 6
                END , state ASC , try ASC

Answer 1

SELECT * FROM `report_table`
WHERE state IN ('downloading', 'error','fail') OR state IS NULL
ORDER BY
    CASE WHEN state = 'downloading' THEN 1
         WHEN state = 'error' AND try<=20 THEN 2
         WHEN state IS NULL THEN 3
         WHEN state = 'error' AND try>20 THEN 4
         WHEN state = 'fail' THEN 5
         ELSE 6
     END , state ASC , try ASC

结果:

id  state       try
3   downloading 1
8   downloading 5
14  downloading 6
6   error       3
10  error       3
12  error       8
15  error       17
7   (null)      0
4   (null)      0
1   (null)      0
11  error       21
2   error       25
9   fail        51

SQL Fiddle 中的示例结果.