在MYSQL中按FIELD排序 [英] Order by FIELD in MYSQL
本文介绍了在MYSQL中按FIELD排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在努力解决这个问题.我有这样一张桌子.
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 |
+-------------+-------+
查询:
select type , COUNT from TABLE order by FIELD(type,'A','B','C','D') ;
如果列type
的值为'A,B,C,D',则它可以正常工作.在某些情况下,某些列的FIELD('A','B','C','D')
顺序可能在table中没有值.在这种情况下,我想将其设置为0并构造一个结果.
It works fine if the column type
has 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 .
D在表中不存在.因此,将其设置为"0".
D is not there in table . So put '0' for it .
显示创建表的输出
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
推荐答案
如何做到这一点:
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') ;
这篇关于在MYSQL中按FIELD排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文