交叉表,为什么不行?
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`
查询结果跟原数据有点出入..
没有出入。
你要像按照顺序排的话，可以：
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
