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

实例

转换前

方案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；

转换后

方案2

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

转换后

实例
转换前

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;

转换后