Mysql高级系列-查询用逗号隔开字符串数据的交集

---

前言

最近遇到一个奇葩的需求，需要在两张表里面对应另一张表的同一条数据的两个用逗号隔开的字符串的数据的交集，需要用到Sql查询来处理，下面就来说一下如何实现。

---

一、创建表

创建用户表test

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int(0) NOT NULL COMMENT 'id',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'address',
  `to_id` int(0) NULL DEFAULT NULL COMMENT 'to_id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, '6,8,10,15', 2);
INSERT INTO `test` VALUES (2, '10,8,15,23', 3);

SET FOREIGN_KEY_CHECKS = 1;

创建测试表user

-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(0) NOT NULL COMMENT 'id',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'addrss',
  `to_id` int(0) NULL DEFAULT NULL COMMENT 'to_id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '5,10,15,20', 3);
INSERT INTO `user` VALUES (2, '8,10,15,30', 2);

SET FOREIGN_KEY_CHECKS = 1;

二、Sql实现

需求：
  求user表里面to_id=3对应的字符串"5,10,15,20"和test表里面to_id=3对应的字符串"10,8,15,23"相同的值10，15。

1、首先我们需要把字符串分解出来

分解user表的字符串：

SELECT
DISTINCT
	substring_index(
		substring_index(
			a.address,
			',',
			b.help_topic_id + 1
		),
		',' ,- 1
	) AS shareholder,
	a.id
FROM
	user a
JOIN mysql.help_topic b ON b.help_topic_id < (
	length(a.address) - length(
		REPLACE (a.address, ',', '')
	) + 1
)
where to_id=3

分解test表的字符串：

SELECT
DISTINCT
	substring_index(
		substring_index(
			a.address,
			',',
			b.help_topic_id + 1
		),
		',' ,- 1
	) AS shareholder,
	a.id
FROM
	test a
JOIN mysql.help_topic b ON b.help_topic_id < (
	length(a.address) - length(
		REPLACE (a.address, ',', '')
	) + 1
)
where to_id=3

2、求他们数据的交集值

SELECT
	ss.shareholder 
FROM
	(
	SELECT DISTINCT
		substring_index( substring_index( a.address, ',', b.help_topic_id + 1 ), ',',- 1 ) AS shareholder,
		a.id 
	FROM
		test a
		JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.address ) - length( REPLACE ( a.address, ',', '' ) ) + 1 ) 
	WHERE
		to_id = 3 
	) ss
	INNER JOIN (
	SELECT DISTINCT
		substring_index( substring_index( a.address, ',', b.help_topic_id + 1 ), ',',- 1 ) AS shareholder,
		a.id 
	FROM
		USER a
		JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.address ) - length( REPLACE ( a.address, ',', '' ) ) + 1 ) 
	WHERE
	to_id = 3 
	) tt ON tt.shareholder = ss.shareholder

3、结论

  首先一个字符串里面可能需求不同，会有重复的值，需要用distinct去重，然后我们把逗号去除，查询出对应的数据。这里主要就是如何把逗号去除，查询数据，其他的就是连表求交集。

---

总结

  以上就是今天要讲的内容，本文简单介绍了如何把用逗号组成的字符串把逗号去除重新求出一个数据的使用，希望对家有所帮助，谢谢。