mysql field不在列表的排在后面,在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 |

+-------------+-------+

QUERY:

select type , COUNT from TABLE order by FIELD(type,'A','B','C','D') ;

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 is not there in table . So put '0' for it .

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

解决方案

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') ;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值