在 MYSQL 中按字段排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13416153/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Order by FIELD in MYSQL
提问by kannanrbk
I am struggling to resolve this . I have a table like this .
我正在努力解决这个问题。我有一张这样的桌子。
+-------------+-------+
| type | COUNT |
+-------------+-------+
| A | 1 |
| C | 5 |
| B | 4 |
+-------------+-------+
I want to query the table and the result must be like this .
我想查询表，结果一定是这样的。
+-------------+-------+
| type | COUNT |
+-------------+-------+
| A | 1 |
| B | 5 |
| C | 9 |
| D | 0 |
+-------------+-------+
QUERY:
询问：
select type , COUNT from TABLE order by FIELD(type,'A','B','C','D') ;
It works fine if the column typehas value for 'A,B,C,D' . In some cases the order by FIELD('A','B','C','D')some columns may not have value in table . In this cases I want to put 0 for it and construct a result .
如果该列type具有 'A,B,C,D' 的值，则它工作正常。在某些情况下，FIELD('A','B','C','D')某些列的顺序在 table 中可能没有值。在这种情况下，我想为它输入 0 并构建一个结果。
D is not there in table . So put '0' for it .
D 不在表中。所以把'0'给它。
SHOW CREATE TABLE OUTPUT
显示创建表输出
CREATE TABLE `Summary` (
`TIMESTAMP` bigint(20) NOT NULL DEFAULT '0',
`type` varchar(50) NOT NULL DEFAULT '',
`COUNT` bigint(19) NOT NULL,
PRIMARY KEY (`TIMESTAMP`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
回答by Joe G Joseph
how abt this:
这如何：
select a.col as type,coalesce (`COUNT`,0) as `count`
from
(select 'A' as col union all
select 'B' as col union all
select 'C' as col union all
select 'D' as col )a
left join Table1 T
on a.col=T.type
order by FIELD(a.col,'A','B','C','D') ;
