mysql实现对某一字段分隔并统计数量

本文参考: 

https://blog.csdn.net/u010827544/article/details/88738264
https://blog.csdn.net/pjymyself/article/details/81668157

本文用到的sql语法及原理, 以上两个链接的博主写得非常详细, 大家若想深入了解, 可移步到上方链接博文.

需求背景:

   如下图所示, 对industry字段中的数据, 按逗号分隔, 并统计每个行业的数量

创建表:

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `org_industry`
-- ----------------------------
DROP TABLE IF EXISTS `org_industry`;
CREATE TABLE `org_industry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `industry` varchar(200) DEFAULT NULL,
  `num` int(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of org_industry
-- ----------------------------
INSERT INTO `org_industry` VALUES ('1', '采矿业,制造业', '3');
INSERT INTO `org_industry` VALUES ('2', '采矿业', '2');
INSERT INTO `org_industry` VALUES ('3', '制造业,金融业', '1');
INSERT INTO `org_industry` VALUES ('4', '房地产业,教育', '2');
INSERT INTO `org_industry` VALUES ('5', '教育', '3');

应用1: 去重查询industry字段按逗号分隔后的列表

SELECT 
		DISTINCT SUBSTRING_INDEX( SUBSTRING_INDEX( a.industry, ',', help_topic_id + 1 ), ',',- 1 ) AS industry 
	FROM
		mysql.help_topic m,
		( SELECT industry FROM org_industry ) a 
	WHERE
		help_topic_id < LENGTH( a.industry ) - LENGTH( REPLACE ( a.industry, ',', '' ) ) + 1

结果:

应用2: 统计industry字段中, 按逗号分隔, 并统计每个行业的数量

select t.industry ,count(*)
from (
	SELECT 
		SUBSTRING_INDEX( SUBSTRING_INDEX( a.industry, ',', help_topic_id + 1 ), ',',- 1 ) AS industry 
	FROM
		mysql.help_topic m,
		( SELECT industry FROM org_industry ) a 
	WHERE
		help_topic_id < LENGTH( a.industry ) - LENGTH( REPLACE ( a.industry, ',', '' ) ) + 1
) t group by t.industry

结果:

应用3: 统计industry字段中, 按逗号分隔的行业, num字段的统计数量

select t.industry, sum(num) 
from (
	SELECT 
		SUBSTRING_INDEX( SUBSTRING_INDEX( a.industry, ',', help_topic_id + 1 ), ',',- 1 ) AS industry , a.num
	FROM
		mysql.help_topic m,
		( SELECT industry , num FROM org_industry ) a 
	WHERE
		help_topic_id < LENGTH( a.industry ) - LENGTH( REPLACE ( a.industry, ',', '' ) ) + 1
) t group by t.industry

结果:

总结: 本文主要用到了 字符串拆分语法： SUBSTRING_INDEX（str, delim, count）

参数名	解释
str	需要拆分的字符串
delim	分隔符，通过某字符进行拆分
count	当 count 为正数，取第 n 个分隔符之前的所有字符； 当 count 为负数，取倒数第 n 个分隔符之后的所有字符。

并借助mysql自带的help_topic 表的 help_topic_id 来作为变量，因为 help_topic_id 是自增的，当然也可以用其他表的自增字段辅助. 从而得到分割后的逗号数量, 继而两个拆分语法嵌套, 进行循环操作取得分割后的数据.