SQL-DAY 2 （SQL的查询语句）

SQL-DAY 2 （SQL的查询语句）

文章目录

• 一、行列转换
• • • 纵表转横表
    • 横表转纵表

---

一、行列转换 纵表转横表

1.先根据姓名进行分组
select username from stu group by username
2.decode函数

case 字段名
	when 值1 then 结果1
	when 值2 then 结果2
	···
	else 结果n
end

实例

转换前

usernamecoursescore张三mysql90张三hive85张三python79李四mysql86李四hive89李四python92

方案1

#查询
select username,
	sum(case course when 'mysql' then score else 0 end ) mysql,
	sum(case course when 'hive' then score else 0 end ) hive,
	sum(case course when 'python' then score else 0 end ) python
	from stu group by username;

#查询，并将查询结果保存成view视图
create view vstu as select username，
	sum(case course when 'mysql' then score else 0 end ) mysql,
	sum(case course when 'hive' then score else 0 end ) hive,
	sum(case course when 'python' then score else 0 end ) python
	from stu group by username;
#查询视图内容
select * from vstu；

转换后

usernamemysqlhivepython张三908579李四868992

方案2

select username,group_concat(course),group_concat(score) 
	from stu group by username;

转换后

usernamegroup_concat(course)group_concat(course)张三mysql,hive,python90,85,79李四mysql,hive,python86,89,92 横表转纵表

实例
转换前

usernamemysqlhivepython张三908579李四868992

select username ,'mysql' course , mysql score from vstu
union all
select username ,'hive' course , hive score from vstu
union all
select username ,'python' course , python score from vstu;

转换后

usernamecoursescore张三mysql90张三hive85张三python79李四mysql86李四hive89李四python92