一步一個腳印，一天一道大數(shù)據(jù)面試題

博主希望能夠得到大家的點(diǎn)贊收藏支持！非常感謝
點(diǎn)贊，收藏是情分，不點(diǎn)是本分。祝你身體健康，事事順心！

行轉(zhuǎn)列

假設(shè)我們有一張名為 sales_data 的表，其中包含 product_id（產(chǎn)品 ID）、category（類別）和 sales_amount（銷售金額）這幾列的數(shù)據(jù)。

步驟：

1. group by id
2. 聚合函數(shù)sum/max/min，里面套一個 if / case when
3. as 列名

樣例數(shù)據(jù)：

-- 樣例 SQL
SELECT * FROM students;
+-----------+------------+-------------+
| stu_id    | subject    | score       |
+-----------+------------+-------------+
| 1         | Chinese    | 80          |
| 1         | Math       | 70          |
| 1         | English    | 75          |
| 2         | Chinese    | 77          |
| 2         | Math       | 60          |
| 2         | English    | 80          |
+-----------+------------+-------------+

開始行轉(zhuǎn)列：

SELECT stu_id,SUM(IF(subject = 'Chinese', score, 0) AS chinese_score),SUM(IF(subject = 'Math', score, 0) AS math_score),SUM(IF(subject = 'English', score, 0) AS english_score)
FROM students
GROUP BY stu_id;+-----------+--------------+-------------+---------------+
| stu_id    | chinese_score| math_score  | english_score |
+-----------+--------------+-------------+---------------+
| 1         | 80           | 70          | 75            |
| 2         | 77           | 60          | 80            |
+-----------+--------------+-------------+---------------+

我是近未來，祝你變得更強(qiáng)！