First, there are three tables. CNo corresponds to the course. Here I paste it.
Master table
Name of person
Query by regular
SELECT s. SName, c. CName, s2.SCgrade
From s s inner join SC s2 ON s2.SNo = s. SNo INNER JOIN C c ON c. CNo = s2.CNo
The result is as follows:
However, this is a horizontal table, not the result I want to see.
We want to see the following results:
So what should we do?
Method 1:
Copy codeThe Code is as follows:
SELECT w. SName,
Sum (case when w. CNo = 1 then w. SCgrade ELSE 0 END) AS 'China ',
Sum (case WHEN w. CNo = 2 THEN w. SCgrade ELSE 0 END) AS 'mate ',
Sum (case when w. CNo = 3 then w. SCgrade ELSE 0 END) 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 FROM SC c GROUP BY c. SNo ))
AS w group by w. SName
Method 2:
Copy codeThe Code is as follows:
SELECT s. SName,
Sum (case when s2.CNo = 1 then s2.SCgrade ELSE 0 END) AS 'China ',
Sum (case WHEN s2.CNo = 2 THEN s2.SCgrade ELSE 0 END) AS 'mate ',
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 on c. CNo = s2.CNo
Group by s. SNo,
S. SName
This is my work experience. Let's summarize it. If this happens, you can refer.