现要求将图1的数据,使用sql查询后,得到图2的结果
图1:
图2:
构造数据:
CREATE TABLE `t_score` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`course` varchar(255) DEFAULT NULL,
`score` int(10) DEFAULT NULL,
`age` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('1', '张三', 'Linux', '85', '一');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('2', '张三', 'Sql', '90', '二');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('3', '张三', 'Java', '95', '三');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('4', '李四', 'Linux', '79', '四');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('5', '李四', 'Sql', '75', '五');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('6', '李四', 'Java', '95', '六');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('7', '王五', 'Linux', '94', '七');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('8', '王五', 'Sql', '83', '八');
INSERT INTO `bookstore`.`t_score` (`id`, `name`, `course`, `score`, `age`) VALUES ('9', '王五', 'Java', '75', '九');
思路:先按名字分组,再对需要展示的列进行聚合
select
`name`,
SUM(if(course='Linux',score,null)) as 'Linux',
SUM(if(course='Sql',score,null)) as 'Sql',
SUM(if(course='Java',score,null)) as 'Java'
from t_score
group by `name`;
应题目要求,score是数字类型的,我们可以使用sum来聚合
如果需要字符串类型的`age`进行聚合,我们可以使用group_concat来达到相同的效果
select
`name`,
group_concat(if(course='Linux',age,null)) as 'Linux',
group_concat(if(course='Sql',age,null)) as 'Sql',
group_concat(if(course='Java',age,null)) as 'Java'
from t_score
group by `name`;
附加另一种解决方法:(sql太长了)
select
`name`,
max(`Linux`) as 'Linux',
max(`Sql`) as 'Sql',
max(`Java`) as 'Java'
from(
select
`name`,
case
when course='Linux' then score
end as 'Linux',
case
when course='Sql' then score
end as 'Sql',
case
when course='Java' then score
end as 'Java'
from t_score
group by `name`,course
) a group by `name`;
--