sql 的优化问题！

[lfb]

我有一个表叫成绩表corse，其中s_no,c_no为主键，分别表示学号和课程号，score 是成绩分数。如果用select * from corse 的话成绩是坚着排的，我想变成横的表于是用的这样的语句：select b1.s_no,b1.c_no,b1.score,b2.c_no,b2.score,b3.c_no,b3.score,
b4.c_no,b4.score,b5.c_no,b5.score,b6.c_no,b6.score,b7.c_no,b7.score,
b8.c_no,b8.score
from corse as b1,corse as b2,corse as b3,corse as b4,corse as b5,corse as b6,corse as b7,corse as b8
where b1.s_no=b2.s_no and b2.s_no=b3.s_no and b3.s_no=b4.s_no
and b4.s_no=b5.s_no and b5.s_no=b6.s_no and b6.s_no=b7.s_no
and b7.s_no=b8.s_no and b1.c_no=1 and b2.c_no=2 and b3.c_no =3 and b4.c_no=4 and b5.c_no=5 and b6.c_no=6 and b7.c_no=7 and b8.c_no=8
这样是不是要牺牲很大的性能？我的表中我90000行，用的3秒选出所有的信息。

[j9988]

看看分组查询：case when c_no=1 then .......else ...end

[蓝天]

用case就可以了。
select s_no,sum(case c_no=1 then score else 0 end) as score1,
sum(case c_no=2 then score else 0 end) as score2,
sum(case c_no=3 then score else 0 end) as score3,
sum(case c_no=4 then score else 0 end) as score4,
sum(case c_no=5 then score else 0 end) as score5,
sum(case c_no=6 then score else 0 end) as score6,
sum(case c_no=7 then score else 0 end) as score7,
sum(case c_no=8 then score else 0 end) as score8,
from corse group by s_no

[蓝天]

select s_no,sum(case when c_no=1 then score else 0 end) as score1,
sum(case when c_no=2 then score else 0 end) as score2,
sum(case when c_no=3 then score else 0 end) as score3,
sum(case when c_no=4 then score else 0 end) as score4,
sum(case when c_no=5 then score else 0 end) as score5,
sum(case when c_no=6 then score else 0 end) as score6,
sum(case when c_no=7 then score else 0 end) as score7,
sum(case when c_no=8 then score else 0 end) as score8,
from corse group by s_no

[昵称被占用了]

一下语句能快不少：

select b1.s_no,b1.c_no,b1.score,b2.c_no,b2.score,b3.c_no,b3.score,
b4.c_no,b4.score,b5.c_no,b5.score,b6.c_no,b6.score,b7.c_no,b7.score,
b8.c_no,b8.score
from corse as b1,corse as b2,corse as b3,corse as b4,corse as b5,corse as b6,corse as b7,corse as b8
where b1.s_no=b2.s_no
and b1.s_no=b3.s_no
and b1.s_no=b4.s_no
and b1.s_no=b5.s_no
and b1.s_no=b6.s_no
and b1.s_no=b7.s_no
and b1.s_no=b8.s_no
and b1.c_no=1 and b2.c_no=2 and b3.c_no =3 and b4.c_no=4 and b5.c_no=5 and b6.c_no=6 and b7.c_no=7 and b8.c_no=8

[蓝天]

又写错了，多了一个逗号，呵呵
select s_no,sum(case when c_no=1 then score else 0 end) as score1,
sum(case when c_no=2 then score else 0 end) as score2,
sum(case when c_no=3 then score else 0 end) as score3,
sum(case when c_no=4 then score else 0 end) as score4,
sum(case when c_no=5 then score else 0 end) as score5,
sum(case when c_no=6 then score else 0 end) as score6,
sum(case when c_no=7 then score else 0 end) as score7,
sum(case when c_no=8 then score else 0 end) as score8
from corse group by s_no

[lfb]

好象在性能上没有什么改善！

[My_first]

看看，过过SQL隐