文章目录
- 行转列
- 思路
- 实现行转列
- 为什么要用`SUM`等聚合函数
- 列转行
- 思路
- 回顾下`Union`
- 实现列转行
- Reference
行转列
思路
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
上面的语句中的执行顺序如下:
- FROM Products1`先把表读进来
+--------------+-------+--------+
| product_id | store | price |
+--------------+-------+--------+
| 0 | store1 | 95 |
| 1 | store1 | 70 |
| 0 | store2 | 100 |
| 1 | store2 | NULL |
| 0 | store3 | 105 |
| 1 | store3 | 80 |
+--------------+----- --+-------+
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 |
+--------------+----- --+-------+
- 然后才是
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 ;
执行流程跟上面查总价的类似:
- FROM Products1`先把表读进来
+--------------+-------+--------+
| product_id | store | price |
+--------------+-------+--------+
| 0 | store1 | 95 |
| 1 | store1 | 70 |
| 0 | store2 | 100 |
| 1 | store2 | NULL |
| 0 | store3 | 105 |
| 1 | store3 | 80 |
+--------------+----- --+-------+
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 |
+--------------+----- --+-------+
- 此时,每个
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. 每个产品在不同商店的价格