mysql列转行以及年月分组实例

MySQL是一个关系型数据库管理系统，支持将列转换为行进行实例。列转行是指将一列中的数据按照某种方式转换成多行。

1. 列转行

实例1：将一列中的数据用逗号隔开

假设有一个名为fruit的表，其中有一个名为name的列，里面存储有多种水果的名称，如“苹果”、“香蕉”等。我们需要将这些水果名称按照逗号隔开，变成新的多行数据，可以使用以下SQL语句实现：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', n.n), ',', -1) AS fruit
FROM fruit
CROSS JOIN
(
 SELECT a.N + b.N * 10 + 1 n
 FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) a,
 (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) b
 ORDER BY n
) n
WHERE n.n <= 1 + (LENGTH(name) - LENGTH(REPLACE(name, ',', ''))))
ORDER BY fruit;

其中，SUBSTRING_INDEX函数是用来取出name列中的第n个逗号之前或之后的子串，最终得到对应的水果名称。CROSS JOIN是用来取出所有需要的n值，以便于对每种水果进行遍历。

实例2：将一列拆分成多个列

假设有一个名为students的表，其中有一个名为scores的列，里面存储了多个学生的五科成绩，使用逗号隔开。我们需要将这些成绩分别拆分成五个独立的列，变成新的多行数据，可以使用以下SQL语句实现：

SELECT id, SUBSTRING_INDEX(scores, ',', 1) AS subject1, SUBSTRING_INDEX(SUBSTRING_INDEX(scores, ',', 2), ',', -1) AS subject2, SUBSTRING_INDEX(SUBSTRING_INDEX(scores, ',', 3), ',', -1) AS subject3, SUBSTRING_INDEX(SUBSTRING_INDEX(scores, ',', 4), ',', -1) AS subject4, SUBSTRING_INDEX(SUBSTRING_INDEX(scores, ',', 5), ',', -1) AS subject5
FROM students;

其中，SUBSTRING_INDEX函数根据逗号分隔符来分割scores列，以得到每个学生的五科成绩。最后根据学生id和分割后的成绩，生成五列新的数据。

1. 年月分组

实例1：统计不同年份的订单数量

假设有一个名为orders的表，其中有一个名为created_at的列，里面存储了下单时间。我们需要统计出每年的订单数量，可以使用以下SQL语句实现：

SELECT YEAR(created_at) AS year, COUNT(*) AS count
FROM orders
GROUP BY YEAR(created_at);

其中，YEAR函数用来提取created_at列中的年份，GROUP BY语句用于按照年份来分组所有的订单数据，并统计每个分组中的数量。

实例2：统计每个月份的商品销售额

假设有一个名为sales的表，其中有一个名为create_time的列，里面存储了销售时间。另外，还有一个名为price的列，里面存储了商品销售价格。我们需要统计出每个月份的商品销售总额，可以使用以下SQL语句实现：

SELECT DATE_FORMAT(create_time, '%Y-%m') AS month, SUM(price) AS total_price
FROM sales
GROUP BY DATE_FORMAT(create_time, '%Y-%m');

其中，DATE_FORMAT函数用于提取create_time列中的年份和月份，并将其格式化为“YYYY-MM”的形式。GROUP BY语句用于按照月份来分组所有的销售数据，并计算每个分组中的销售总额。

以上就是MySQL列转行以及年月分组的实例攻略。