MySQL轻松将一行数据转换成多行mysql一行转换多行

我必须告诉你这件事。
去年我在一家电子商务公司工作，是的，就是那种每天和数据库打交道的公司。
我们有一个表叫order，这就是订单表。
每个订单有很多产品，但保存时，每个订单都有一条记录，产品信息分组在一起，并用逗号分隔。
这个东西很难分析，所以我想到了把它拆开，把每个产品放在自己的一排。

例如，让我们看看，在这个订单中，OrderID是1 2 3 ，OrderDate是2 02 0-01 -01 ，CustomerName是Tom，ProductID是1 ,2 ,3 ，ProductName是Apple，Orange，Banana，Quantity是1 ,2 ,1 我们希望它看起来像这样：
|订单编号 |订购日期 |客户名称 |产品编号 |产品名称 |数量 | |--------|----------|----------|----------|----------||----------| | 1 2 3 | 1 2 3 2 02 0-01 -01 |汤姆| 1 |苹果| 1 | | 1 2 3 | 1 2 3 2 02 0-01 -01 |汤姆| 2 |橙色| 2 | | 1 2 3 | 1 2 3 2 02 0-01 -01 |汤姆| 3 |香蕉| 1 |
当时尝试了几种方法，最终使用了SUBSTRING_INDEX和UNION ALL。
这个问题比较复杂，我来给大家解释一下。

首先，您必须分隔三个字段 ProductID、ProductNames 和 Quantity。
SUBSTRING_INDEX 函数使用起来特别方便。
比如想要获取ProductID中的第一个产品ID，可以这样写：
sql SUBSTRING_INDEX(产品 ID, ',', 1 )
该函数第一个参数是字符串，第二个参数是分隔符，第三个参数是要获取的参数。
1 是第一个，-1 是最后一个。

然后你必须合并这些分离的数据。
此处必须使用 UNION ALL。
您必须制作原始订单的多份副本，并且每份副本都有一个序列号，例如 seq。
然后按照这个数字顺序划分每个字段。

让我把完整的代码发给你，这样会更清楚：
sql 选择 订单编号， 订单日期， 客户姓名， SUBSTRING_INDEX(SUBSTRING_INDEX(ProductIDs, ',', seq), ',', -1 ) AS ProductID, SUBSTRING_INDEX(SUBSTRING_INDEX(产品名称, ',', seq), ',', -1 ) AS 产品名称, SUBSTRING_INDEX(SUBSTRING_INDEX(Quantity, ',', seq), ',', -1 ) AS 数量 来自（ 选择 订单编号， 订单日期， 客户姓名， 产品编号， 产品名称， 数量， 1 ASseq 来自订单 全部 选择 订单编号， 订单日期， 客户姓名， 产品编号， 产品名称， 数量， 2 下一个 来自订单 全部 选择 订单编号， 订单日期， 客户姓名， 产品编号， 产品名称， 数量， 3 ASseq 来自订单 ） tmp 哪里 SUBSTRING_INDEX(SUBSTRING_INDEX(ProductIDs, ',', seq), ',', -1 ) 不为空
这条SQL首先将每个订单复制3 次（可以根据实际情况调整这个数量），然后每次复制都有一个seq序号。
然后通过 SUBSTRING_INDEX 和 seq 的组合拆分每个字段。
最后，使用 WHERE 子句过滤掉那些空白行。

嗯，关键在于使用SUBSTRING_INDEX和UNION ALL来划分数据。
第一次尝试，尝试了好几次但数据不匹配，我终于弄清楚了。
尝试一下，它会起作用的。

哦，顺便说一下，我以前也经历过这种情况。
有一次，当我编写此 SQL 时，我忘记添加 WHERE 子句来过滤掉空行。
结果出现了一堆空文件，让我很着急。
后来我才发现问题就出在这里。
因此，在编写 SQL 时，细节很重要。

您还有其他问题吗？比如数据量特别大的时候这个方法会不会很慢？这个我不敢乱说，因为我还没有尝试过。

mysql中怎么实现列转行

上周，一位客户问我如何在 MySQL 中将列转换为行。
我给了他们两个方法。
我觉得这很有趣。
让我跟你谈谈。

第一种方法：使用UNION ALL谋生
这个方法很有趣。
虽然MySQL没有直接UNPIVOT，但是也可以用UNION ALL来实现。
假设有一个销售表，其中包含 Product_id、product_name、sales_mount 和 sales_data 列。
如果要将产品名称、sale_quantity、sale_date三列改为两列（属性和值），可以这样写：
sql SELECT Product_id、'product_name' AS 属性、product_name AS 值 来自销售 全联盟 SELECT Product_id，属性“sales_sales”AS，CAST(sales_sales AS CHAR) AS 值 来自销售 全联盟 SELECT Product_id、'sales_date' AS 属性、sales_date AS 值 来自销售 按产品 ID、属性排序；
要点： 1 . 每个SELECT语句提取一列数据，然后使用字符串常量（例如'product_name'）作为属性列。
2 .若要将sales_amount数字类型转换为字符串，请使用CAST（sales_amount AS CHAR），否则连接后类型将不匹配。
3 .最后使用UNION ALL将结果连接起来，并使用ORDER BY进行排序，使它们看起来有序。

缺点： 这种方法相当直观，但是如果列很多的话就有点烦人了。
您必须为每一列编写一条 SELECT 语句，这很容易出错。
上次帮同事用这个方法，直接丢了一根柱子，挣扎了好久。

第二种：使用CROSS JOIN + CASE表达式
这种方式比较灵活，特别是当列名需要动态改变的时候。
还是用sales表，可以这样写：
sql SELECT s.product_id, c.col_name AS 属性, CASE c.col_name WHEN 'product_name' THEN s.product_name 当 'sales_mount' 时 CAST(s.sales_mount AS CHAR) 当 'data_sales' 那么 s.data_sales 结束值 来自销售 CROSS JOIN (SELECT "product_name" AS col_name UNION ALL SELECT "sales_mount" UNION ALL SELECT "sales_data") c 按 s.product_id、c.col_name 排序；
要点： 1 、子查询首先生成一个临时表c，列出了所有要传输的列名。
2 . CROSS JOIN 将每行数据与列名组合成笛卡尔积。
3 、CASE表达式根据列名c.col_name返回对应的值，数值类型也必须转为字符串。
优点： 如果列名是动态的，例如从数据库中检查它们，然后更改行，则此方法将起作用。
我在上海的一个购物中心参与了一个项目。
客户需求变化很大，列表也经常变化。
我用的就是这个方法，灵活多了。
注意事项： 1 、数据类型一致：行转换后，值列必须是同一类型（例如转换为字符串），否则可能会出现串联错误。
上次我在北京培训的时候，就有一个学生陷入了这个陷阱。
数据类型不统一，一切都乱七八糟。
2 .性能问题：当表特别大时，UNION ALL可能会有点慢。
我在北京一家公司做优化，一个几百G的表用这个方法直接卡了。
最后使用临时表进行处理解决了组。
您可以尝试批处理或使用临时表。
3 、动态列名：如果列名是动态的，就得使用动态SQL存储过程，比较费力。
我之前在上海的一个项目中尝试过。
它有效，但代码非常丑陋。
最后客户说算了，手动改吧。
摘要： 对于简单的场景，先使用UNION ALL，写起来简单直接。
对于复杂场景或者列名动态变化，可以使用CROSS JOIN + CASE。
虽然写起来比较费力，但是很灵活。
对于MySQL 8 .0+，可以考虑使用JSON函数（例如JSON_OBJECT），但需要额外的解析。
这取决于具体情况。

无论如何，这取决于你。
两种方法都有其优点和缺点。
关键要看你的具体需求。
我还在思考一个问题，就是如果表中有NULL值怎么办。
我得再考虑一下。

MySQL数据转换一行变多行mysql一行转为多行

UNPIVOT 是 MySQL 1 2 .1 及更高版本的一项功能。
如果您的 MySQL 版本低于 1 2 .1 ，请不要使用 UNPIVOT，而应使用动态 SQL。


指定“Employee_ID”属性、“First_Name”和“First_Name”值。
来自员工 全联盟 选择员工 ID、姓氏和姓氏 来自员工 全联盟 选择员工 ID、“EML”、EML 来自员工 全联盟 选择员工 ID、电话、电话 来自员工；
这是一个两难的境地。
除非您确定您的版本支持它，否则不要相信直接使用 UNPIVOT。