如图一,通过sq了实现图二:
图一如下:
图二如下:
附建表语句&初始化数据语句：
CREATE TABLE `sc` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_name` varchar(32) NOT NULL DEFAULT '', `subject` varchar(32) NOT NULL DEFAULT '', `score` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into sc(id,user_name,subject,score) values (1,"张三","语文",80),(2,"张三","数学",90),(3,"张三","英语",70),(4,"张三","生物",85), (5,"李四","语文",80),(6,"李四","数学",90),(7,"李四","英语",70),(8,"李四","生物",85);
以下有简单易懂的&万能通用的两种写法:
1.简单易懂的一种写法，由于一名学生一门课只有一个分数，所以下面sql中max函数完全可以换为sum等其他聚合函数。
select `user_name`, max(case `subject` when '语文' then IFNULL(`score`, 0) else 0 end) as '语文', max(case `subject` when '数学' then IFNULL(`score`, 0) else 0 end) as '数学', max(case `subject` when '英语' then IFNULL(`score`, 0) else 0 end) as '英语', max(case `subject` when '生物' then IFNULL(`score`, 0) else 0 end) as '生物'from `sc`group by `user_name`;
运行结果:
2.不需要提前知道有多少门课程，动态生成列，万能通用的一种写法。
set @splice_sql = null;SELECT
GROUP_CONCAT(DISTINCT
CONCAT('max(if(subject=''',subject,''', score, 0)) as ''',subject, '''')) into @splice_sqlfrom sc;set @splice_sql = CONCAT('select user_name,', @splice_sql, ' from sc group by user_name');prepare bella_test from @splice_sql;execute bella_test;DEALLOCATE prepare bella_test;
运行结果
