MySQL中group_concat函数的使用说明以及行列转换

首先讲一下concat函数

1.concat函数

这是MySQL5API里面的解释
CONCAT(str1,str2,…)
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。或许有一个或多个参数。 如果所有参数均为非二进制字符串，则结果为非二进制字符串。 如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如： SELECT CONCAT(CAST(int_col AS CHAR), char_col)

注意：
如果所有参数均为非二进制字符串，则结果为非二进制字符串
如果自变量中含有任一二进制字符串，则结果为二进制字符串
一个数字参数被转为与之相等的二进制字符串格式，如要避免这种情况，可使用显式类型cast

在这里简单演示一下：

mysql> select concat('str','ing');
+---------------------+
| concat('str','ing') |
+---------------------+
| string              |
+---------------------+
1 row in set
mysql> select concat('str','ing',null
);
+--------------------------+
| concat('str','ing',null) |
+--------------------------+
| NULL                     |
+--------------------------+
1 row in set

2.group_concat

GROUP_CONCAT(expr)
该函数返回带有来自一个组的连接的非NULL值的字符串结果。其完整的语法如下所示：

GROUP_CONCAT([DISTINCT] expr [,expr ...]

             [ORDER BY {unsigned_integer | col_name | expr}

                 [ASC | DESC] [,col_name ...]]

             [SEPARATOR str_val])

使用例子：

表结构

CREATE TABLE `article_in_category` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `article_id` int(11) unsigned NOT NULL,
 `category_id` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `article_id_INDEX` (`article_id`),
 KEY `category_id_INDEX` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

mysql> INSERT INTO `article_in_category` (`id`, `article_id`, `category_id`) VALUES (NULL, '1', '1'),
(NULL, '1', '2'),(NULL, '1', '3'),(NULL, '2', '4'),(NULL, '2', '3'),(NULL, '2', '5'),(NULL, '3', '1'),
(NULL, '3', '5'),(NULL, '3', '6'),(NULL, '4', '8');

Query OK, 10 rows affected
Records: 10  Duplicates: 0  Warnings: 0

查询数据

mysql> select * from `article_in_category`;
+----+------------+-------------+
| id | article_id | category_id |
+----+------------+-------------+
|  1 |          1 |           1 |
|  2 |          1 |           2 |
|  3 |          1 |           3 |
|  4 |          2 |           4 |
|  5 |          2 |           3 |
|  6 |          2 |           5 |
|  7 |          3 |           1 |
|  8 |          3 |           5 |
|  9 |          3 |           6 |
| 10 |          4 |           8 |
| 11 |          1 |           1 |
| 12 |          1 |           2 |
| 13 |          1 |           3 |
| 14 |          2 |           4 |
| 15 |          2 |           3 |
| 16 |          2 |           5 |
| 17 |          3 |           1 |
| 18 |          3 |           5 |
| 19 |          3 |           6 |
| 20 |          4 |           8 |
+----+------------+-------------+
20 rows in set

行转换成列

使用group_concat函数结合group by函数，获取文章的分类及分类id：

mysql> select article_id,group_concat(category_id order by category_id asc) from `article_in_category` group by article_id;
+------------+----------------------------------------------------+
| article_id | group_concat(category_id order by category_id asc) |
+------------+----------------------------------------------------+
|          1 | 1,1,2,2,3,3                                        |
|          2 | 3,3,4,4,5,5                                        |
|          3 | 1,1,5,5,6,6                                        |
|          4 | 8,8                                                |
+------------+----------------------------------------------------+
4 rows in set

这是由于上面插入数据的时候不小心执行了两遍。这就实现了简单的行转换成列。

列转换成行

列转行比行转列复杂，对于列内容是用分隔符分隔的数据，我们可以使用substring_index()函数进行分割输出，并结合笛卡尔积来实现循环。

mysql> CREATE TABLE `option2` (
 `category_id` int(10) unsigned NOT NULL COMMENT '分类id',
 `name` varchar(100) NOT NULL COMMENT '名称集合'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected

mysql> INSERT INTO `option2` (`category_id`, `name`) VALUES
(1, '大,中,小 '),
(2, '奔驰,宝马'),
(3, '2015,2016,2017,2018'),
(4, '1m,2m');
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from `option2`;
+-------------+---------------------+
| category_id | name                |
+-------------+---------------------+
|           1 | 大,中,小            |
|           2 | 奔驰,宝马           |
|           3 | 2015,2016,2017,2018 |
|           4 | 1m,2m               |
+-------------+---------------------+
4 rows in set

mysql> select a.category_id,substring_index(substring_index(a.name,',',b.category_id),',',-1) as name from `option2` as a
join `option2` as b on b.category_id<=(length(a.name) - length(replace(a.name,',',''))+1)
order by a.category_id,b.category_id;

+-------------+------+
| category_id | name |
+-------------+------+
|           1 | 大   |
|           1 | 中   |
|           1 | 小   |
|           2 | 奔驰 |
|           2 | 宝马 |
|           3 | 2015 |
|           3 | 2016 |
|           3 | 2017 |
|           3 | 2018 |
|           4 | 1m   |
|           4 | 2m   |
+-------------+------+
11 rows in set