TIDB 行转列和列转行操作 (附 SQL 实战)

2024-03-15
北京
本文字数：914 字
阅读完需：约 3 分钟

作者： paulli 原文来源：https://tidb.net/blog/0ad3e804

TiDB 是一款常用的分布式数据库，广泛应用于各种类型的应用程序和数据存储需求。我们经常需要对表进行行转列或列转行的操作，以满足不同的分析或报表需求。本文将详细介绍 TiDB 中的行转列和列转行操作，并提供相应的 SQL 语句进行操作。

行转列

行转列操作指的是将表格中一行数据转换为多列数据的操作。由于当前 TiDB 暂时未兼容 oracle pivot 函数，我们通过 CTE 递归功能，可以动态实现列值不固定内容的情况进行转换，示例如下：

mysql> select * from t10;+------------+| id         |+------------+| abc,djd,ed || dd,oe     |+------------+2 rows in set (0.00 sec)mysql> WITH RECURSIVE split_values AS (    ->   SELECT     ->     id,   ->     SUBSTRING_INDEX(id, ',', 1) AS value,    ->     SUBSTRING(id, LENGTH(SUBSTRING_INDEX(id, ',', 1)) + 2) AS remaining_values    ->   FROM t10    ->   UNION ALL    ->   SELECT     ->     id,    ->     SUBSTRING_INDEX(remaining_values, ',', 1),    ->     SUBSTRING(remaining_values, LENGTH(SUBSTRING_INDEX(remaining_values, ',', 1)) + 2)    ->   FROM split_values    ->   WHERE remaining_values != '' )    -> SELECT     ->   id,    ->   value    -> FROM split_values    -> WHERE value != '';+------------+-------+| id         | value |+------------+-------+| abc,djd,ed | abc   || dd,oe     | dd   || abc,djd,ed | djd   || dd,oe     | oe   || abc,djd,ed | ed   |+------------+-------+5 rows in set (0.00 sec)

复制代码

列转行

列转行操作指的是将表格中多列数据转换为一行数据的操作。在 TIDB 兼容已 group_concat，可以使用此函数进行快速实现，示例如下：

mysql> select * from t11;+------+| id   |+------+| sdj || sdd || xdd || xcd |+------+4 rows in set (0.01 sec)mysql> select group_concat(id) from t11;+------------------+| group_concat(id) |+------------------+| sdj,sdd,xdd,xcd  |+------------------+1 row in set (0.01 sec)