SELECT D.name FROM (SELECT B.number,CASE(A.c_name WHEN '数据库技术' THEN score ELSE 0 END) AS shu_score,CASE(A.c_name WHEN '操作系统' THEN score ELSE 0 END) AS cao_score
FROM course A INNER JOIN sle_course B ON B.c_number = A.c_number
GROUP BY B.number) C INNER JOIN jbqk D ON D.number = C.number
WHERE C.shu_score>C.cao_scoreselect A.number,B.name FROM
(select number,SUM(CASE WHEN score<=80 THEN 1 ELSE 0 END) as shu
from sle_course group by number) A
INNER JOIN jbqk B ON B.number = A.number WHERE A.shu < 1select count(*) from (select DISTINCT number from sle_course where score < 60) A
FROM course A INNER JOIN sle_course B ON B.c_number = A.c_number
GROUP BY B.number) C INNER JOIN jbqk D ON D.number = C.number
WHERE C.shu_score>C.cao_scoreselect A.number,B.name FROM
(select number,SUM(CASE WHEN score<=80 THEN 1 ELSE 0 END) as shu
from sle_course group by number) A
INNER JOIN jbqk B ON B.number = A.number WHERE A.shu < 1select count(*) from (select DISTINCT number from sle_course where score < 60) A
列名 'c501' 无效。
列名 'c502' 无效。
非常感谢你的帮助,你的第一条语句改为下面语句就通过了,其他二句都对:
第一个问题("数据库技术"比"操作系统"课程成绩高的学生姓名)
SELECT D.name FROM (SELECT B.number,SUM(CASE WHEN A.c_name = '数据库技术' THEN score ELSE 0 END)
AS shu_score,SUM(CASE WHEN A.c_name = '操作系统' THEN score ELSE 0 END) AS cao_score
FROM course A INNER JOIN sle_course B ON B.c_number = A.c_number
GROUP BY B.number) C INNER JOIN jbqk D ON D.number = C.number
WHERE C.shu_score>C.cao_score另外非常感谢大家的帮助,问题已经解决了。大家加分。
select count(*) from (select number,SUM(CASE WHEN score <= 60 THEN 1 ELSE 0 END) AS c_score from sle_course group by number) a where a.c_score = 0