MySQL 列转行函数全方位解析|极客笔记

MySQL 列转行函数全方位解析

近年来，数据处理和分析在各个行业中的应用越来越广泛。在数据处理过程中，常常需要将多个列合并为一行，以便于统计和分析。MySQL 提供了一种非常方便的列转行函数，可以帮助我们轻松地完成这一操作。

1. 概述

在 MySQL 中，列转行指的是将多列的数据转换为一行。这在一些数据分析和报表统计的场景下非常实用。MySQL 提供了两个主要的列转行函数，分别是UNION和UNION ALL。这两个函数的功能非常相似，用法也几乎一样，但是它们之间有一些细微的差别，后面会详细介绍。

2. `UNION`函数的使用

UNION函数是 MySQL 中最常用的列转行函数之一。它可以将多个查询结果合并成一张表。下面是一个使用UNION函数的示例：

SELECT column_name
FROM table1
WHERE condition
UNION
SELECT column_name
FROM table2
WHERE condition;

在上面的示例中，UNION函数会将table1和table2的查询结果合并为一张表，并且去除重复的行。

在实际使用中，我们可能会遇到需要对多个表进行列转行的情况。例如，我们有两张表products和orders，其中products表包含了产品名称和产品价格，orders表包含了订单号和产品名称。我们希望将这两张表的数据合并，以便于统计每个产品的销售金额。可以使用下面的查询语句来实现：

SELECT product_name, SUM(product_price) AS total_price
FROM (
  SELECT product_name, product_price
  FROM products
  UNION ALL
  SELECT product_name, 0
  FROM orders
) AS merged_table
GROUP BY product_name;

在上面的示例中，我们使用了UNION ALL函数将products和orders两张表的数据合并为一张表。其中，SELECT product_name, product_price FROM products查询了products表中的产品名称和产品价格，SELECT product_name, 0 FROM orders查询了orders表中的产品名称和一个固定值 0。因为UNION ALL函数会保留所有的行，所以我们使用了一个子查询，并对orders表中的数据进行了处理，以确保合并后的表中有与products表对应的行。

3. `UNION ALL`函数的使用

与UNION函数相比，UNION ALL函数有一个明显的区别，即UNION ALL函数保留所有的行，而不会去除重复的行。这在一些需要包含重复数据的场景下非常有用。下面是一个使用UNION ALL函数的示例：

SELECT column_name
FROM table1
WHERE condition
UNION ALL
SELECT column_name
FROM table2
WHERE condition;

在上面的示例中，UNION ALL函数会将table1和table2的查询结果合并为一张表，并保留所有的行，包括重复的行。

使用UNION ALL函数的示例与使用UNION函数的示例类似，唯一的区别是我们不需要处理重复的行。例如，我们可以将之前的示例代码中的UNION ALL函数替换为UNION函数，然后运行相同的查询语句，得到的结果将会去除重复的行。

4. 示例代码

为了更好地理解和实践列转行函数，下面给出了一个完整的示例代码，并展示了其运行结果。

首先，创建两张测试表products和orders：

CREATE TABLE products (
  id INT PRIMARY KEY,
  product_name VARCHAR(50),
  product_price DECIMAL(10, 2)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  order_number VARCHAR(50),
  product_name VARCHAR(50)
);

INSERT INTO products (id, product_name, product_price)
VALUES (1, 'Product A', 10.00),
       (2, 'Product B', 20.00),
       (3, 'Product C', 30.00);

INSERT INTO orders (id, order_number, product_name)
VALUES (1, 'Order 001', 'Product A'),
       (2, 'Order 002', 'Product A'),
       (3, 'Order 003', 'Product B'),
       (4, 'Order 004', 'Product B'),
       (5, 'Order 005', 'Product C');

接下来，运行列转行函数，并统计每个产品的销售金额：

SELECT product_name, SUM(product_price) AS total_price
FROM (
  SELECT product_name, product_price
  FROM products
  UNION ALL
  SELECT product_name, 0
  FROM orders
) AS merged_table
GROUP BY product_name;

最后，得到的结果如下：

+-------------+-------------+
| product_name| total_price |
+-------------+-------------+
| Product A   | 20.00       |
| Product B   | 50.00       |
| Product C   | 30.00       |
+-------------+-------------+

在上面的运行结果中，可以看到每个产品的销售金额已经被正确地统计出来了。