MySQL 数据表 纵横 转换 Sql语句 (group by)

案例一

id, student, course, mark 
1,  张三,    语文,  90 
2,  李四,    语文,  80 
3,  张三,    数学,  70 
4,  李四,    数学,  80 

现在想用转换表的方法转成二维表输出，要求格式是：

student, 语文, 数学 
张三,    90,  70 
李四,    80,  80 

sql语句

select student, 
    sum(if(course='语文',mark,0)) as 语文, 
    sum(if(course='数学',mark,0)) as 数学
from table1 
group by student

小插曲

1   a
2   b
1   c
2   d
合并为
1   a,c
2   b,d


select group_concat(column_b,separator ',') from table_name group by column_a;

案例二

想要的效果图

数据库表如下

表 point_user_log

表 point

表 point_cat

sql语句

select c.cat_name,b.point_name,
sum(if(star=1,1,0)) as 'star1', 
sum(if(star=2,1,0)) as 'star2', 
sum(if(star=3,1,0)) as 'star3', 
sum(if(star=4,1,0)) as 'star4', 
sum(if(star=5,1,0)) as 'star5',
sum(star)/count(star) as staravg
from `point_user_log` as a 
left join `point` as b on a.point_id=b.point_id 
left join `point_cat` as c on b.cat_id=c.id 
group by a.point_id