例如：我有一个学生表 Student
UID Name
1 张三
2 李四
另外有个课程表 course
RID CID courseID
1 2 语文
2 1 数学
还有一个成绩表 Score
CID UID score
1 2 11
1 1 13
2 1 22
2 2 90
最后的结果是
name 数学 语文
张三 13 22
李四 11 90

CREATE TABLE Student(UID INT,Name NVARCHAR(10));
INSERT INTO Student VALUES(1,N'张三'),(2,N'李四')
CREATE TABLE course(RID INT,CID INT,courseID NVARCHAR(10));
INSERT INTO course VALUES(1,2,N'语文'),(2,1,N'数学')
CREATE TABLE Score(CID INT,UID INT,Score INT);
INSERT INTO Score VALUES(1,2,11),(1,1,13),(2,1,22),(2,2,90)
--solution1
;WITH cte AS(
SELECT s.Name,c.courseID,r.Score
FROM Student s JOIN score r ON s.UID=r.UID
JOIN course c ON r.CID=c.CID)
SELECT Name,
MAX(CASE WHEN courseID=N'数学' THEN Score ELSE 0 END) AS N'数学',
MAX(CASE WHEN courseID=N'语文' THEN Score ELSE 0 END) AS N'语文'
FROM cte GROUP BY Name
--solution2
;WITH cte AS(
SELECT s.Name,c.courseID,r.Score
FROM Student s JOIN score r ON s.UID=r.UID
JOIN course c ON r.CID=c.CID)
SELECT * FROM cte c
PIVOT
(
MAX(score) FOR courseID IN ([数学],[语文])
) AS PT
DROP TABLE Student,course,score

用联表查询，inner join