一个学生成绩表:sc(name,sub,score);
5条记录:
name sub score
li 数 59
li 英 60
li 中 61
je 英 80
wu 中 90
写SQL语句,结果为
姓名 数学 英语 中文
li 59 60 61
je NULL 60 NULL
wu NULL NULL 90
感觉用三个自连接,左外连接可以完成,但是
select distinct s1.name, s1.score as 'w', s2.score as 'e',s3.score as 'c',count(s1.score) limit 2
from sc s1 left join sc s2 on(s1.name=s2.name and s1.sub!=s2.sub) left join sc s3 on(s1.name=s3.name and s1.sub!=s3.sub and s3.sub!=s2.sub)
group by s1.name;
这样子会有科目成绩不对,因为默认了第一个表的成绩为数学,纠结纠结
求教
5条记录:
name sub score
li 数 59
li 英 60
li 中 61
je 英 80
wu 中 90
写SQL语句,结果为
姓名 数学 英语 中文
li 59 60 61
je NULL 60 NULL
wu NULL NULL 90
感觉用三个自连接,左外连接可以完成,但是
select distinct s1.name, s1.score as 'w', s2.score as 'e',s3.score as 'c',count(s1.score) limit 2
from sc s1 left join sc s2 on(s1.name=s2.name and s1.sub!=s2.sub) left join sc s3 on(s1.name=s3.name and s1.sub!=s3.sub and s3.sub!=s2.sub)
group by s1.name;
这样子会有科目成绩不对,因为默认了第一个表的成绩为数学,纠结纠结
求教
from sc s1 left join sc s2 on(s1.name=s2.name and s1.sub!=s2.sub) left join sc s3 on(s1.name=s3.name and s1.sub!=s3.sub and s3.sub!=s2.sub)
group by s1.name;
请参考本版版主博客
sum(if(sub='数',score,null)) as `数学`,
sum(if(sub='英',score,null)) as `英语`,
sum(if(sub='中',score,null)) as `中文`
From sc
Group by name
http://blog.csdn.net/redwood_lin/archive/2007/04/03/1550606.aspx