MySQL统计标签数量，行转列

文章目录

标签是一个很多业务系统里都常用的功能，我们需要统计每个标签对应的文章数量。我们的数据如下：

文章表(article)：

id

tags

…

1

aaa,bbb

…

2

bbb,ccc

…

3

ddd

…

我们需要统计每个标签对应的文章数量，期望输出以下内容

tag

cnt

aaa

1

bbb

2

ccc

1

ddd

1

那么这个SQL要怎么写呢？先看最终SQL：

SELECT tag, COUNT(*) cnt
  FROM
(
  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(c.tags, ',', n.n), ',', -1) tag
    FROM article c CROSS JOIN tally n
   WHERE n.n <= 1 + (CHAR_LENGTH(c.tags) - CHAR_LENGTH(REPLACE(c.tags, ',', ''))) 
) q
 WHERE CHAR_LENGTH(tag) > 0 
 GROUP BY tag

如果你直接运行上面的SQL，很可能会遇到SQL错误 Table ‘tally’ doesn’t exist 。 是的，这个SQL需要有一个辅助表tally. 你可以使用以下SQL创建这个辅助表：

CREATE TABLE tally(n INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO tally (n)
SELECT NULL
 FROM 
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
;

article 表的SQL：

CREATE TABLE `article`  (
  `id` int(0) NOT NULL,
  `tags` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

INSERT INTO `article` VALUES (1, 'aaa,bbb,');
INSERT INTO `article` VALUES (2, 'bbb,ccc,');
INSERT INTO `article` VALUES (3, 'ddd,');