mysql 函数

一、字段值是按照逗号分割保存

SELECT GROUP_CONCAT(option_name) busiTypeName,GROUP_CONCAT(option_code) option_code FROM sys_option_conf WHERE option_code REGEXP (
SELECT REPLACE (( SELECT busi_type FROM cont_base_info WHERE id = '878228366862319616'), ',','|'))

二、将查询出来的值进行逗号分割保存

 GROUP_CONCAT(option_name) busiTypeName

GROUP_CONCAT(m.configure_type ORDER BY m.configure_type)->排序分组

三、相关信息

select a.* ,b.* from (SELECT
cbi.*,soc.option_name as contStatusName,soc1.option_name as contTypeName
FROM cont_base_info cbi
left join sys_option_conf soc on cbi.cont_status = soc.option_code
left join sys_option_conf soc1 on cbi.cont_type = soc1.option_code
WHERE cbi.data_status = 100101 and cbi.id = '878228366862319616' ) a ,
(SELECT GROUP_CONCAT(option_name) busiTypeName,GROUP_CONCAT(option_code) option_code FROM sys_option_conf WHERE option_code REGEXP (
SELECT REPLACE (( SELECT busi_type FROM cont_base_info WHERE id = '878228366862319616'), ',','|'))) b
where a.busi_type = b.option_code

四、根据类型进行个数计算

select a.vehicle_set_id,a.totalCount,a.implementedCount,a.startedCount,a.complatedCount,(a.complatedCount/ a.totalCount) as percent,case when a.startedCount =0 and a.complatedCount =0 then '待执行' when a.implementedCount =0 and a.startedCount =0 then '已完成' else '执行中' end taskStatus
from
(SELECT
t.vehicle_set_id,
count(distinct t.task_branch) as totalCount,
sum(case when t.task_status ='100501' then 1 else 0 end) as implementedCount,
sum(case when t.task_status ='100502' then 1 else 0 end) as startedCount,
sum(case when t.task_status ='100503' then 1 else 0 end) as complatedCount
FROM dai_task_view t group by t.vehicle_set_id) a