SQL中GROUP_CONCAT相关用法

测试数据

SELECT ps.id,ps.session_name,ps.start_time,ps.end_time
FROM product_session_rel psr
LEFT JOIN policy_session ps ON psr.session_id = ps.id AND ps.is_deleted = 'false' 
WHERE psr.sub_product_id = 1566005568733085696 and psr.is_deleted = 'false'

 

 GROUP_CONCAT函数基本用法

-- GROUP_CONCAT函数用法：分组拼接字符串
-- GROUP_CONCAT(ps.session_name) 分组拼接字符串，默认使用逗号分割
-- GROUP_CONCAT(ps.session_name SEPARATOR ';') 分组拼接字符串，使用指定字符分割
-- GROUP_CONCAT(ps.start_time,ps.end_time SEPARATOR ';') 分组拼接两个字段，字段之间没有符号分割，数据之间有符号分割
-- GROUP_CONCAT(CONCAT(ps.start_time,'-',ps.end_time) SEPARATOR ';') 分组拼接两个字段，字段之间使用指定符号分割，数据之间用分号分割

SELECT GROUP_CONCAT(ps.session_name SEPARATOR ';')
FROM product_session_rel psr
LEFT JOIN policy_session ps ON psr.session_id = ps.id AND ps.is_deleted = 'false' 
WHERE psr.sub_product_id = '1566005568733085696' and psr.is_deleted = 'false'

 

GROUP_CONCAT(CONCAT(ps.start_time,'-',ps.end_time) SEPARATOR ';')

SELECT GROUP_CONCAT(CONCAT(ps.start_time,'-',ps.end_time) SEPARATOR ';')
FROM product_session_rel psr
LEFT JOIN policy_session ps ON psr.session_id = ps.id AND ps.is_deleted = 'false' 
WHERE psr.sub_product_id = '1566005568733085696' and psr.is_deleted = 'false'

 

 GROUP_CONCAT与 CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END 联合使用

SELECT 
GROUP_CONCAT(
CASE WHEN ps.session_name IS NULL THEN CONCAT(ps.start_time,'-',ps.end_time)
WHEN ps.session_name IS NOT NULL THEN ps.session_name
ELSE '这不会触发' END 
SEPARATOR ';')
FROM product_session_rel psr
LEFT JOIN policy_session ps ON psr.session_id = ps.id AND ps.is_deleted = 'false' 
WHERE psr.sub_product_id = '1566005568733085696' and psr.is_deleted = 'false';