First is three tables, CNO corresponds to the course, where I pasted.
Primary table
Name list
Follow the general query
SELECT S.sname, C.cname,s2. Scgrade
From S INNER JOIN SC s2 on S2. SNo = S.sno INNER JOIN c c on c.cno = s2. CNo
So the result is this.
But this is not the result I want to see.
We want to see this result:
So what do we do?
The first method of writing:
Copy Code code as follows:
SELECT W.sname,
SUM (case when w.cno= 1 then w.scgrade ELSE 0) as ' language ',
SUM (case when W.cno =2 THEN w.scgrade ELSE 0) as ' math ',
SUM (case when w.cno= 3 then w.scgrade ELSE 0) as ' English '
From
(SELECT s.sno,s.sname, S2.) CNo, S2. Scgrade from S-INNER JOIN SC s2 on S2. SNo = S.sno WHERE s.sno in (SELECT C.sno to SC C GROUP by C.sno))
As W GROUP by W.sname
The second way:
Copy Code code as follows:
SELECT S.sname,
sum (case when S2.) cno= 1 then S2. Scgrade ELSE 0 end) as ' language ',
sum (case when S2.) CNo =2 THEN S2. Scgrade ELSE 0 end) as ' math ',
sum (case when S2.) cno= 3 then S2. Scgrade ELSE 0 end) as ' English '
From
s s INNER JOIN SC s2 on S2. SNo = S.sno
INNER JOIN c c on c.cno = s2. CNo
GROUP by S.sno,
S.sname
This is my work encountered situation, summed up. If you have encountered this situation, you can refer to the following.
