MySQL – 行列转换
在MySQL中,有时需要将表中的行数据转换为列数据,或将列数据转换为行数据。这种操作被称为“行列转换”。
MySQL提供了两个常用的函数来实现行列转换:GROUP BY
和PIVOT
。
阅读更多:MySQL 教程
将行数据转换为列数据
使用GROUP BY函数
GROUP BY
函数可以将相同列数据的行数据进行合并,并将其合并后的值作为新的一列数据。
例如,我们有如下一张存储订单信息的表格:
OrderID | CustomerID | OrderDate | Product |
---|---|---|---|
1 | 101 | 2020-01-01 | Apple |
2 | 102 | 2020-01-02 | Banana |
3 | 101 | 2020-01-03 | Orange |
4 | 102 | 2020-01-04 | Grape |
5 | 101 | 2020-01-05 | Mango |
如果需要将每个客户购买的所有商品按照订单日期列出,如下所示:
Date | Customer101 | Customer102 |
---|---|---|
2020-01-01 | Apple | NULL |
2020-01-02 | NULL | Banana |
2020-01-03 | Orange | NULL |
2020-01-04 | NULL | Grape |
2020-01-05 | Mango | NULL |
可以使用如下语句:
CASE
函数可以根据条件返回不同的值,这里我们根据CustomerID
来判断是哪个客户。MAX
函数可以帮助我们将列中的多个值合并成单个值。
使用PIVOT函数
MySQL没有提供官方的PIVOT
函数,但是我们可以使用自定义函数来实现PIVOT
功能。
下面是一个简单的PIVOT
自定义函数的示例:
该函数接受四个参数:原始查询语句、GROUP BY
字段、需要被PIVOT的字段、替换的字符。
使用该函数的例子:
将会返回如下结果:
将列数据转换为行数据
使用UNION函数
UNION
函数可以将多个查询的结果集合并成一个。
例如,我们有如下一张存储国家和语言信息的表格:
Country | Language1 | Language2 | Language3 |
---|---|---|---|
China | Mandarin | Cantonese | Shanghainese |
Japan | Japanese | NULL | NULL |
Thailand | Thai | NULL | NULL |
如果需要将语言信息展开成行数据,如下所示:
Country | Language |
---|---|
China | Mandarin |
China | Cantonese |
China | Shanghainese |
Japan | Japanese |
Thailand | Thai |
可以使用如下语句:
该语句使用了三个查询,分别查询Language1
、Language2
、Language3
的值,然后使用UNION
将结果合并。
使用自连接
另一种将列数据转换为行数据的方法是使用自连接。自连接是通过使用JOIN
将表与自身连接得到的结果。
例如,我们有如下一张存储客户交易信息的表格:
CustomerID | TransactionDate | TransactionAmount |
---|---|---|
101 | 2020-01-01 | 100 |
101 | 2020-02-01 | 200 |
102 | 2020-01-03 | 150 |
102 | 2020-02-05 | 300 |
如果需要将每个客户交易信息按时间展开成行数据,如下所示:
CustomerID | TransactionDate | TransactionAmount |
---|---|---|
101 | 2020-01-01 | 100 |
101 | 2020-02-01 | 200 |
102 | 2020-01-03 | 150 |
102 | 2020-02-05 | 300 |
可以使用如下语句:
该语句使用了自连接,得到了每个客户的交易信息,并按时间展开成行数据。
总结
MySQL中的行列转换可以使用GROUP BY
、PIVOT
、UNION
和自连接等方法实现。这些方法各有优缺点,在使用时需要根据具体场景选择合适的方法。