一步一个脚印,一天一道大数据面试题
博主希望能够得到大家的点赞收藏支持!非常感谢
点赞,收藏是情分,不点是本分。祝你身体健康,事事顺心!
行转列
假设我们有一张名为 sales_data 的表,其中包含 product_id(产品 ID)、category(类别)和 sales_amount(销售金额)这几列的数据。
步骤:
group by id
- 聚合函数
sum/max/min
,里面套一个if / case when
as 列名
样例数据:
-- 样例 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 |
+-----------+------------+-------------+
开始行转列:
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 |
+-----------+--------------+-------------+---------------+
我是近未来,祝你变得更强!