【MySQL笔记】行转列+列转行+行转列为什么用SUM()

行转列

思路

GROUP BY+聚合函数

实现行转列

SELECT 
  product_id,
  SUM(IF(store = 'store1', price, NULL)) AS 'store1',
  SUM(IF(store = 'store2', price, NULL)) AS 'store2',
  SUM(IF(store = 'store3', price, NULL)) AS 'store3' 
FROM
  Products1 
GROUP BY product_id ;

用case when...then...end也可以，SUM也可以换成MAX、MIN等聚合函数

SELECT 
  product_id,
  MIN(CASE WHEN store = 'store1' THEN price END) AS 'store1',
  MIN(CASE WHEN store = 'store2' THEN price END) AS 'store2',
  MIN(CASE WHEN store = 'store2' THEN price END) AS 'store3' 
FROM
  Products1 
GROUP BY product_id ;

为什么要用SUM等聚合函数

大部分人可能都有这个疑惑，为什么还要加个聚合函数SUM在这

我们熟悉的聚合函数+GROUP BY的使用场景应该就是“统计各产品在所有store的总价/均价”之类，比如

SELECT product_id, SUM(price) FROM Products1 GROUP BY product_id

上面的语句中的执行顺序如下：

1. FROM Products1`先把表读进来

+--------------+-------+--------+
| product_id   | store  | price |
+--------------+-------+--------+
| 0            | store1 | 95    |
| 1            | store1 | 70    |
| 0            | store2 | 100   |
| 1            | store2 | NULL  |
| 0            | store3 | 105   |
| 1            | store3 | 80    |
+--------------+----- --+-------+

2. GROUP BY product_id把表中数据按product_id分组

+--------------+-------+--------+
| product_id   | store  | price |
+--------------+-------+--------+
| 0            | store1 | 95    |
| 0            | store2 | 100   |
| 0            | store3 | 105   |
+--------------+----- --+-------+
| 1            | store1 | 70    |
| 1            | store2 | NULL  |
| 1            | store3 | 80    |
+--------------+----- --+-------+

3. 然后才是SELECT，其中SUM(price)计算各个分组price字段的总和（NULL不参与计算）

+--------------+------------+
| product_id   | SUM(price) |
+--------------+------------+
| 0            | 300        |
| 1            | 150        |

回到我们上面行转列的语句，我们只看一个字段先

SELECT 
  product_id,
  SUM(IF(store = 'store1', price, NULL)) AS 'store1'
FROM
  Products1 
GROUP BY product_id ;

执行流程跟上面查总价的类似：

1. FROM Products1`先把表读进来

+--------------+-------+--------+
| product_id   | store  | price |
+--------------+-------+--------+
| 0            | store1 | 95    |
| 1            | store1 | 70    |
| 0            | store2 | 100   |
| 1            | store2 | NULL  |
| 0            | store3 | 105   |
| 1            | store3 | 80    |
+--------------+----- --+-------+

2. GROUP BY product_id把表中数据按product_id分组

+--------------+--------+--------+
| product_id   | store  | price |
+--------------+-------+--------+
| 0            | store1 | 95    |
| 0            | store2 | 100   |
| 0            | store3 | 105   |
+--------------+----- --+-------+
| 1            | store1 | 70    |
| 1            | store2 | NULL  |
| 1            | store3 | 80    |
+--------------+----- --+-------+

3. 此时，每个product_id的组里各有三条数据，我们现在只需要store1的price，因此需要IF(store = 'store1', price, NULL)进行过滤，不是store1的price全部当作NULL，而NULL不参与聚合函数运算，也就是说，这里SUM实际运算的对象只有一个数据，就是store1的price (product_id为1的组里为95，product_id为2的组里为70)，所以这个聚合函数换成MIN也可以

列转行

思路

每次查一行，然后用union拼起来

回顾下Union

用于连接两个以上的 SELECT 语句的结果组合到一个结果集合，并去除重复的行

SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION [ALL]
SELECT column1, column2, ...
FROM table2
WHERE condition2
[ORDER BY column1, column2, ...];

• 操作中的列数和数据类型必须相同

• UNION 操作符在合并结果集时会去除重复行，而 UNION ALL 不会去除重复行

• union 连接的语句中只会出现一个order by （不包含子查询中的）

  select * from(select  *  from table order by a)
  union
  select * from (select * from table1 order by b)
  union
  select * from (select * from table2 order by c)
  order by d;
  

  • 多个order可以放在子查询

  select * from(select  *  from table order by a)
  union
  select * from (select * from table1 order by b)
  union
  select * from (select * from table2 order by c);
  

实现列转行

SELECT product_id, 'store1' store, store1 price FROM products WHERE store1 IS NOT NULL
UNION
SELECT product_id, 'store2' store, store2 price FROM products WHERE store2 IS NOT NULL
UNION
SELECT product_id, 'store3' store, store3 price FROM products WHERE store3 IS NOT NULL;

Reference

1795. 每个产品在不同商店的价格