展开全部
Mysql 示例:
1. 创建t_student表CREATE TABLE `62616964757a686964616fe59b9ee7ad9431333365636634t_student` (
`id` int NOT NULL AUTO_INCREMENT , -- 自增ID
`studentID` varchar(20) NULL , -- 学号
`studentName` varchar(20) NULL , -- 姓名
`subject` varchar(50) NULL , -- 科目
`score` double NULL , -- 成绩
PRIMARY KEY (`id`) -- 主键设置
);
2. 填充数据
3. 查询SELECT
t_student.studentID,
t_student.studentName,
(SELECT b.score FROM t_student AS b WHERE b.studentID = t_student.studentID AND b.`subject` = '语文') AS chineseScore,
(SELECT b.score FROM t_student AS b WHERE b.studentID = t_student.studentID AND b.`subject` = '数学') AS mathematicsScore,
(SELECT b.score FROM t_student AS b WHERE b.studentID = t_student.studentID AND b.`subject` = '英语') AS englishScore,
SUM(t_student.score) AS totalScore
FROM t_student
GROUP BY t_student.studentID
ORDER BY totalScore DESC