实现一行字符串逗号，截取分开成列，再count计算

最新推荐文章于 2023-08-07 16:37:07 发布

rzrenyu

最新推荐文章于 2023-08-07 16:37:07 发布

阅读量114

点赞数

分类专栏： sql 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/rzrenyu/article/details/121121158

版权

sql 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

SELECT
	COUNT(*)
FROM
	(
		SELECT
			SUBSTRING_INDEX(
				SUBSTRING_INDEX(
					r.card_id_list,
					',',
					help_topic_id + 1
				),
				',' ,- 1
			) AS num
		FROM
			(
				SELECT
					GROUP_CONCAT(card_id_list) card_id_list
				FROM
					bgd_collect_player_day
				WHERE
					rid = 1148424
			) AS r,
			mysql.help_topic
		WHERE
			help_topic_id < LENGTH(r.card_id_list) - LENGTH(REPLACE(r.card_id_list, ',', '')) + 1
	) AS w

INSERT INTO `future_bgd`.`bgd_collect_player_day` (`id`, `create_date`, `rid`, `nick`, `channel`, `platform`, `group_type`, `card_id_list`) VALUES ('92', '2020-11-01', '1148424', NULL, '1001', '0', '10', '112');
INSERT INTO `future_bgd`.`bgd_collect_player_day` (`id`, `create_date`, `rid`, `nick`, `channel`, `platform`, `group_type`, `card_id_list`) VALUES ('93', '2020-07-24', '1029767', NULL, NULL, '0', '1', '1,2,3,4,5,6,7,9,11');
INSERT INTO `future_bgd`.`bgd_collect_player_day` (`id`, `create_date`, `rid`, `nick`, `channel`, `platform`, `group_type`, `card_id_list`) VALUES ('94', '2020-07-24', '1029767', NULL, NULL, '0', '2', '15,16,19');
INSERT INTO `future_bgd`.`bgd_collect_player_day` (`id`, `create_date`, `rid`, `nick`, `channel`, `platform`, `group_type`, `card_id_list`) VALUES ('95', '2020-07-24', '1029767', NULL, NULL, '0', '3', '26,27,29,34');
INSERT INTO `future_bgd`.`bgd_collect_player_day` (`id`, `create_date`, `rid`, `nick`, `channel`, `platform`, `group_type`, `card_id_list`) VALUES ('96', '2020-07-24', '1029767', NULL, NULL, '0', '4', '39,41,42,44,48');
INSERT INTO `future_bgd`.`bgd_collect_player_day` (`id`, `create_date`, `rid`, `nick`, `channel`, `platform`, `group_type`, `card_id_list`) VALUES ('97', '2020-07-24', '1146071', NULL, NULL, '0', '1', '1,2,3,4,5,6,7,11,12');
INSERT INTO `future_bgd`.`bgd_collect_player_day` (`id`, `create_date`, `rid`, `nick`, `channel`, `platform`, `group_type`, `card_id_list`) VALUES ('98', '2020-07-24', '1146071', NULL, NULL, '0', '2', '13,14,15,16,17,18,19,20,23');
INSERT INTO `future_bgd`.`bgd_collect_player_day` (`id`, `create_date`, `rid`, `nick`, `channel`, `platform`, `group_type`, `card_id_list`) VALUES ('99', '2020-07-24', '1146071', NULL, NULL, '0', '3', '25,26,27,28,29,30,31');
INSERT INTO `future_bgd`.`bgd_collect_player_day` (`id`, `create_date`, `rid`, `nick`, `channel`, `platform`, `group_type`, `card_id_list`) VALUES ('100', '2020-07-24', '1146071', NULL, NULL, '0', '4', '37,38,39,40,41,42,43,45,47,48');
INSERT INTO `future_bgd`.`bgd_collect_player_day` (`id`, `create_date`, `rid`, `nick`, `channel`, `platform`, `group_type`, `card_id_list`) VALUES ('101', '2020-07-24', '1146071', NULL, NULL, '0', '5', '49,50,51,52,53,54,56,59,60');