本文共 1293 字,大约阅读时间需要 4 分钟。
1 场景:对指定的字段进行分组,用GROUP_CONCAT对另外的一个字段进行连接,但是需要保证去重,
构造数据如下:SELECT GROUP_CONCAT(DISTINCT password SEPARATOR ';') FROM user GROUP BY name
查询结果:
2 场景:一个字段中有多个标签,标签用逗号分隔,需要得到所有不重复的标签转为行
SELECT DISTINCT substring_index(substring_index( run.result_label,',',b.help_topic_id + 1),',' ,- 1) AS result_label FROM tb_ucpaas_task_run_319 AS run JOIN mysql.help_topic b ON b.help_topic_id < (length(run.result_label) - length(replace (run.result_label, ',', '')) + 1)
得到如下结果:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num FROM mysql.help_topic WHERE help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1
LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1
得到有多少个字符串,即得help_topic_id最大不能大于的值,SELECT中里层
SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1
得到从左开始一直待到第几个字符串的大字符串,外层得到最后一个字符,即为依次得到每一个字符串。
参考:https://blog.csdn.net/pjymyself/article/details/81668157count和sum根据条件进行统计
select count(if(num=3,1,null)),sum(if(num=3,num,0)) from t_count_sum
返回结果