SELECT b.StudentID,SUM(CASE WHEN b.subjectID=1 THEN b.score ELSE 0 END )AS [subject語文],
SUM(CASE WHEN b.subjectID=2 THEN b.score ELSE 0 END) AS [subjectname2數學],
SUM(CASE WHEN b.subjectID=3 THEN b.score ELSE 0 END )AS [subjectname3英語]
FROM bsubject a JOIN bscore b ON a.id=b.subjectid
GROUP BY b.studentid
SUM(CASE WHEN b.subjectID=2 THEN b.score ELSE 0 END) AS [subjectname2數學],
SUM(CASE WHEN b.subjectID=3 THEN b.score ELSE 0 END )AS [subjectname3英語]
FROM bsubject a JOIN bscore b ON a.id=b.subjectid
GROUP BY b.studentid
,subjectname1(语文)=(select score from bscore where subjectid=1 and studentid=a.studentid)
,.....
from bscore a
group by studentid
http://www.csdn.net/expert/topic/928/928809.xml?temp=8.317202E-02
(select score from bscore T where T.studentid = bscore.studentid and T.subjectid = 1) as 语文,
(select score from bscore T where T.studentid = bscore.studentid and T.subjectid = 2) as 数学,
(select score from bscore T where T.studentid = bscore.studentid and T.subjectid = 3) as 英语,
from bscore
group by studentid
(select score from bscore where bscore.studentid = T.studentid and bscore.subjectid = 1) as 语文,
(select score from bscore where bscore.studentid = T.studentid and bscore.subjectid = 2) as 数学,
(select score from bscore where bscore.studentid = T.studentid and bscore.subjectid = 3) as 英语,
from bscore T
group by studentid
from bscore
LEFT JOIN bscore T1 on bscore.studentid = T1.studentid and T1.subjectid = 1
LEFT JOIN bscore T2 on bscore.studentid = T2.studentid and T2.subjectid = 2
LEFT JOIN bscore T3 on bscore.studentid = T3.studentid and T3.subjectid = 3
group by studentid不用 Group By,用 Distinct 按同样思路还可写出几种答案