先看一下数据
想要从中获取每种类型车辆的总数
如: 通信车4辆 、指挥车2辆 、监测车1辆
- 使用通配符%。
select
sum(case when car_type like '%通信车%' then 1 else '0' end) as '通信',
sum(case when car_type like '%指挥车%' then 1 else '0' end) as '指挥',
sum(case when car_type like '%监测车%' then 1 else '0' end) as '监测',
sum(case when car_type like '%消防车%' then 1 else '0' end) as '消防',
sum(case when car_type like '%救护车%' then 1 else '0' end) as '救护'
from tb_emergency_resource_car
- 使用MySQL提供的字符串函数locate()函数。
select
sum(case when locate( '通信车',car_type) then 1 else '0' end) as '通信',
sum(case when locate( '指挥车',car_type) then 1 else '0' end) as '指挥',
sum(case when locate( '监测车',car_type) then 1 else '0' end) as '监测',
sum(case when locate( '消防车',car_type) then 1 else '0' end) as '消防',
sum(case when locate( '救护车',car_type) then 1 else '0' end) as '救护'
from tb_emergency_resource_car
刚开始以为 like 和when 一起用会报错 就用了locate()
后面发现是写错了
都可以用