交叉表,为什么不行?解决思路

交叉表,为什么不行?
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

喜欢 (0)赏

[搞代码]

分享 (0)