drop table if exists Test;
create table Test(
`id` int not null auto_increment primary key,
`name` varchar(50) not null,
`subject` varchar(50) null,
`score` int null
)engine=myisam;insert into test(`name`,`subject`,`score`) values('zhangsan','chinese',60);
insert into test(`name`,`subject`,`score`) values('zhangsan','math',75);
insert into test(`name`,`subject`,`score`) values('zhangsan','engish',90);insert into test(`name`,`subject`,`score`) values('lisi','chinese',80);
insert into test(`name`,`subject`,`score`) values('lisi','math',90);
insert into test(`name`,`subject`,`score`) values('lisi','engish',50);
select `name`,sum(case `subject` when 'chinese' then score else 0 end) as 'chinese',
sum(case `subject` when 'math' then score else 0 end) as 'math',
sum(case `subject` when 'engish' then score else 0 end) as 'engish'
from test
group by `name`查询结果跟原数据有点出入..
create table Test(
`id` int not null auto_increment primary key,
`name` varchar(50) not null,
`subject` varchar(50) null,
`score` int null
)engine=myisam;insert into test(`name`,`subject`,`score`) values('zhangsan','chinese',60);
insert into test(`name`,`subject`,`score`) values('zhangsan','math',75);
insert into test(`name`,`subject`,`score`) values('zhangsan','engish',90);insert into test(`name`,`subject`,`score`) values('lisi','chinese',80);
insert into test(`name`,`subject`,`score`) values('lisi','math',90);
insert into test(`name`,`subject`,`score`) values('lisi','engish',50);
select `name`,sum(case `subject` when 'chinese' then score else 0 end) as 'chinese',
sum(case `subject` when 'math' then score else 0 end) as 'math',
sum(case `subject` when 'engish' then score else 0 end) as 'engish'
from test
group by `name`查询结果跟原数据有点出入..
解决方案 »
- 关于mysql数据库Transfer的疑问
- mysql->一个数据库里有两个表结构一样的表,我想复制一个表的内容到另一个表里,但是如果更新第一个表再复制的话第二个表不会更新这是为什么呢
- mysqldumpslow 分析慢查询日志
- 连接mysql数据库时。出现这样的错误: No suitable driver
- 急惨了,mysql隔一段时间服务就停掉了,快跳楼了,各位帮我分析下
- linux下如何启动mysql
- 我想把表里的自增字段按照另一字段的排序进行重构,有办法吗?
- 是不是TYPE=INNODB 可以建外建,但不能建auto_imcrement字段,
- 请教一个php + mysql 求月平均 求年平均的高效算法
- 用TIMESTAMPDIFF计算两个日期的月份差,碰到月底的日期会有问题
- 高手帮忙解决下mysql的1248错误
- 插入mysql数据时,语法检查能关闭么?
你要像按照顺序排的话,可以:select * from test;
select `name`,sum(case `subject` when 'chinese' then score else 0 end) as 'chinese',
sum(case `subject` when 'math' then score else 0 end) as 'math',
sum(case `subject` when 'engish' then score else 0 end) as 'engish'
from test
group by `name` order by id;
query result(2 records)
name chinese math engish
zhangsan 60 75 90
lisi 80 90 50