need
In a database table, the data in a field is separated by special symbols, and now it is necessary to count the number of pieces of data separated by separators.
data preparation
-- ---------------------------- -- Table structure for persons -- ---------------------------- DROP TABLE IF EXISTS `persons`; CREATE TABLE `persons` ( `id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id', `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'name', `age` int(0) NULL DEFAULT NULL COMMENT 'age', `sex` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'sex', `address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'address', `sect` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'School', `skill` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'skills, separated by commas', `power` int(0) NULL DEFAULT NULL COMMENT 'combat power value', `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time', `modify_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'modify time', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 451 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of persons -- ---------------------------- INSERT INTO `persons` VALUES (427, 'Zhang Wuji', 16, 'Male', 'Kunlun Mountain Guangmingding', 'Mingjiao', 'Nine Suns, Tai Chi, Qishangquan ,The great shift of heaven and earth', 99, '2021-11-26 16:29:38', '2021-11-26 16:29:38'); INSERT INTO `persons` VALUES (428, 'Zhao Min', 13, 'Female', 'Dadu', 'Imperial Court', 'Miscellaneous Martial Arts', 30, '2021- 11-26 16:32:15', '2021-11-26 16:32:15'); INSERT INTO `persons` VALUES (429, 'Zhou Zhiruo', 13, 'Female', 'Emei Mountain', 'Emei School', 'Nine Yin Scriptures, Emei Swordsmanship', 80 , '2021-11-26 16:32:40', '2021-11-26 16:32:40'); INSERT INTO `persons` VALUES (430, 'Xiao Zhao', 12, 'Female', 'Kunlun Mountain Guangming Peak', 'Mingjiao', 'Great Teleportation', 40, \ '2021-11-26 16:33:27', '2021-11-26 16:33:27'); INSERT INTO `persons` VALUES (431, 'Yin Li', 12, 'Female', 'Mount Emei', 'Eagle Sect', 'Thousands of Spiders', 50, \ '2021-11-26 16:36:12', '2021-11-26 16:36:12'); INSERT INTO `persons` VALUES (432, 'Yang Xiao', 30, 'Male', 'Kunlun Mountain Guangming Peak', 'Mingjiao', 'The Great Shift of the Universe', 80, ' 2021-11-26 17:12:10', '2021-11-26 17:12:10'); INSERT INTO `persons` VALUES (433, 'Fan Yao', 29, 'Male', 'Kunlun Mountain Bright Peak', 'Mingjiao', 'Star Attraction Dafa', 79, \ '2021-11-26 17:14:03', '2021-11-26 17:14:03'); INSERT INTO `persons` VALUES (434, 'Xie Xun', 31, 'Male', 'Binghuo Island', 'Mingjiao', 'Seven Injuries Boxing', 79, '2021- 11-26 17:15:40', '2021-11-26 17:15:40'); INSERT INTO `persons` VALUES (435, 'Yin Tianzheng', 40, 'Male', 'Emei Mountain', 'Sky Eagle Sect', 'Eagle Claw Grabber', 75, \ '2021-11-26 17:16:49', '2021-11-26 17:16:49'); INSERT INTO `persons` VALUES (436, 'Daikis', 35, 'Female', 'Snake Island', 'Mingjiao', 'Persian Martial Arts', 72, \ '2021-11-26 17:18:48', '2021-11-26 17:18:48'); INSERT INTO `persons` VALUES (437, 'Wei Yixiao', 40, 'Male', 'Kunlun Mountain Bright Peak', 'Mingjiao', 'Ice Sponge Palm', 68, '2021-11-26 17:19:45', '2021-11-26 17:19:45'); INSERT INTO `persons` VALUES (438, 'Song Yuanqiao', 41, 'Male', 'Wudang Mountain', 'Wudang School', 'Tai Chi Gong', 78, '2021- 11-26 17:23:47', '2021-11-26 17:23:47'); INSERT INTO `persons` VALUES (439, 'Yu Lianzhou', 39, 'Male', 'Wudang Mountain', 'Wudang School', 'Tai Chi Gong', 75, '2021- 11-26 17:24:29', '2021-11-26 17:24:29'); INSERT INTO `persons` VALUES (440, 'Yu Daiyan', 38, 'Male', 'Wudang Mountain', 'Wudang School', 'Tai Chi Gong', 75, '2021- 11-26 17:24:55', '2021-11-26 17:24:55'); INSERT INTO `persons` VALUES (441, 'Zhang Songxi', 37, 'Male', 'Wudang Mountain', 'Wudang School', 'Tai Chi Gong', 75, '2021- 11-26 17:25:41', '2021-11-26 17:25:41'); INSERT INTO `persons` VALUES (442, 'Zhang Cuishan', 36, 'Male', 'Binghuo Island', 'Wudang School', 'Tai Chi Gong', 77, '2021- 11-26 17:26:10', '2021-11-26 17:26:10'); INSERT INTO `persons` VALUES (443, 'Yin Liting', 33, 'Male', 'Wudang Mountain', 'Wudang School', 'Tai Chi Gong', 76, '2021- 11-26 17:26:37', '2021-11-26 17:26:37'); INSERT INTO `persons` VALUES (444, 'Mo Shenggu', 30, 'Male', 'Wudang Mountain', 'Wudang School', 'Tai Chi Gong', 70, ' 2021-11-26 17:27:03', '2021-11-26 17:27:03'); INSERT INTO `persons` VALUES (445, 'Empty view', 50, 'Male', 'Shaolin Temple', 'Shaolin School', 'Shaolin Dragon Claw Hand', 80, ' 2021-11-26 17:29:06', '2021-11-26 17:29:06'); INSERT INTO `persons` VALUES (446, 'Kongwen', 49, 'Male', 'Shaolin Temple', 'Shaolin School', 'Great Vajra Finger', 80, '2021 -11-26 17:30:00', '2021-11-26 17:30:00'); INSERT INTO `persons` VALUES (447, 'Kongzhi', 47, 'Male', 'Shaolin Temple', 'Shaolin School', 'Shaolin Jiuyanggong', 82, ' 2021-11-26 17:30:56', '2021-11-26 17:30:56'); INSERT INTO `persons` VALUES (448, 'Emptyness', 45, 'Male', 'Shaolin Temple', 'Shaolin School', 'Shaolin Seventy-two Stunts', 78, \ '2021-11-26 17:31:57', '2021-11-26 17:31:57'); INSERT INTO `persons` VALUES (449, 'Yin Susu', 33, 'Female', 'Binghuo Island', 'Tianyingjiao', 'Miscellaneous Studies', 65, '2021 -11-26 17:32:53', '2021-11-26 17:32:53'); INSERT INTO `persons` VALUES (450, 'Master Tai', 33, 'Female', 'Emei Mountain', 'Emei School', 'Emei Swordsmanship', 80, ' 2021-11-26 17:33:41', '2021-11-26 17:33:41');
Need to count the number of people in each skill?
train of thought
Use sql to split a row of data into multiple rows of data based on commas, and then use group by to count the split data.
Three ways, same principle
- Use the auto-incrementing list in the MySQL library
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(p.skill, ',', temp.help_topic_id + 1), ',' , -1) AS unique_skill, COUNT(p.id) AS count FROM persons AS p JOIN mysql.help_topic AS temp ON temp.help_topic_id < (LENGTH(p.skill) - LENGTH(REPLACE (p.skill, ',', '')) + 1) GROUP BY unique_skill ORDER BY count DESC;
Results of the:
- Self-built self-incrementing list
Create an auto-increment table and add data
-- ---------------------------- -- Records of sys_incre_table -- ---------------------------- CREATE TABLE `sys_incre_table` ( `auto_incre_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment table for loop', PRIMARY KEY (`auto_incre_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------- -- Records of sys_incre_table -- ---------------------------- INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (0); INSERT INTO `my_test`. `sys_incre_table`(`auto_incre_id`) VALUES (1); INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (2); INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (3); INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (4); INSERT INTO `my_test`. `sys_incre_table`(`auto_incre_id`) VALUES (5); INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (6); INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (7); INSERT INTO `my_test`. `sys_incre_table`(`auto_incre_id`) VALUES (8); INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (9); INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (10); INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (11); INSERT INTO `my_test`. `sys_incre_table`(`auto_incre_id`) VALUES (12); INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (13); INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (14); INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (15); INSERT INTO `my_test`. `sys_incre_table`(`auto_incre_id`) VALUES (16); INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (17); INSERT INTO `my_test`. `sys_incre_table`(`auto_incre_id`) VALUES (18); INSERT INTO `my_test`. `sys_incre_table`(`auto_incre_id`) VALUES (19); INSERT INTO `my_test`.`sys_incre_table`(`auto_incre_id`) VALUES (20);
SUBSTRING_INDEX(SUBSTRING_INDEX(p.skill, ',', temp.auto_incre_id + 1), ', ' , -1) AS unique_skill, COUNT(p.id) AS count FROM persons AS p JOIN sys_incre_table AS temp ON temp.auto_incre_id < (LENGTH(p.skill) - LENGTH(REPLACE (p.skill, ',', '')) + 1) GROUP BY unique_skill ORDER BY count DESC;
Results of the:
- Build a self-incrementing list based on existing tables in the database
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(p.skill, ',', temp.id + 1), ',', -1) AS unique_skill, COUNT(p.id) AS count FROM persons AS p JOIN (SELECT (@ROW := @Row + 1) AS id FROM persons AS p, (SELECT @Row := -1) AS temp LIMIT 20) temp ON temp.id < (LENGTH(p.skill) - LENGTH( REPLACE(p.skill, ',', '')) + 1) GROUP BY unique_skill ORDER BY count DESC;
Results of the:
already available The table can be itself, or an existing table in the database (it may not be a sequence table), and the number of rows must be greater than the maximum number of commas in the split field.
Summarize
- The sequence table must start from 0, and the number of lines is related to the longest comma, and the number of lines is at least 1 greater than the number of the longest comma, and 0~1000 can be built.
- Why is it not recommended to use the self-incrementing list mysql.help_topic that comes with MySQL? Because the databases of many companies do not have permission to operate these tables, they cannot be used.