背景
突然收到个需求,要求导出一份数据:
1、如处理详情数据多行数据合并为一行
2、类型字段:是,否,其他,为否的值没有入口,需指定为否
表信息
主表,附属的类型表,系统表,处理记录表,产品表
SELECT * FROM test_case ic WHERE id=11007750; --主表
SELECT * FROM test_case_business_type WHERE relevance_id=11007750 --类型表
SELECT * FROM test_case_subsystem ic WHERE case_id=11007750; --系统表
SELECT * FROM test_case_handle_detail ic WHERE case_id=11007750; --处理记录表
SELECT * FROM test_event_belong_detail WHERE event_id=11007750 --产品表
数据处理和函数
-
子系统,产品,处理步骤,类型都有可能一个主表数据 对应多行;如一个主数据中包含多个子系统,多个产品,多个处理步骤,多个类型,用GROUP_CONCAT 函数处理;
-
类型为否时,不存在关联数据,默认赋值为否,使用 COALESCE 函数处理,有点像nvl;
--合并多行数据
SELECT GROUP_CONCAT(is_financial_bus SEPARATOR ',') FROM test_case_business_type WHERE relevance_id=11000411;
--给null赋值,这个值有 是否其他,3个选项,否为空
SELECT ic.id,COALESCE(icbt.is_financial_bus,'否') FROM test_case ic LEFT JOIN test_case_business_type icbt on ic.ID=icbt.relevance_id;
--产品
SELECT GROUP_CONCAT(ieb.event_belong_name SEPARATOR ',') AS '产品' FROM test_event_belong ieb, test_event_belong_detail iebd WHERE ieb.id=iebd.belong_id AND iebd.event_id=11007750;
--子系统
SELECT GROUP_CONCAT(ics.system_en_name SEPARATOR ',') AS '子系统' FROM test_case_subsystem ics WHERE ics.case_id=68;
--处理详情
SELECT GROUP_CONCAT(ichd.operate_detail SEPARATOR ',') AS '处理详情' FROM test_case_handle_detail ichd WHERE ichd.case_id=11007917;
GROUP_CONCAT
1、功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
2、语法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
说明:通过使用distinct可以排除重复值;如果希望对结果中的值进行排序,可以使用order by子句;separator是一个字符串值,缺省为一个逗号。
COALESCE
COALESCE(value,…)是一个可变参函数,可以使用多个参数。
作用:接受多个参数,返回第一个不为NULL的参数,如果所有参数都为NULL,此函数返回NULL;当它使用2个参数时,和IFNULL函数作用相同。
这个参数使用的场合为:假如某个字段默认是null,你想其返回的不是null,而是比如0或其他值,可以使用这个函数
最终结果
SELECT ic.id AS 'ID',ic.EVENT_TITLE AS '标题',(SELECT basekey_name FROM test_event_basekey WHERE id=ic.EVENT_STATUS) AS '状态',ic.EVENT_CREATE_DATE AS '上报时间',
ic.event_desc AS '上报内容',
(SELECT GROUP_CONCAT(ichd.operate_detail SEPARATOR ',') FROM test_case_handle_detail ichd WHERE ichd.case_id=ic.id) AS '处理详情',
(SELECT GROUP_CONCAT(ics.system_en_name SEPARATOR ',') FROM test_case_subsystem ics WHERE ics.case_id=ic.id) AS '子系统',
(SELECT GROUP_CONCAT(ieb.event_belong_name SEPARATOR ',') FROM test_event_belong ieb, test_event_belong_detail iebd WHERE ieb.id=iebd.belong_id AND iebd.event_id=ic.id) AS '产品',
ic.handler_team_id AS '处理组',ic.event_handler AS '处理人',
COALESCE((SELECT GROUP_CONCAT(is_financial_bus SEPARATOR ',') FROM test_case_business_type WHERE relevance_id=ic.id),'否') AS '金融业务'
FROM test_case ic ;