1 SELECT * FROM SCORE LEFT JOIN (SELECT CNO ,ROUND(AVG(DEGREE),2) AS AVG_SCORE FROM SCORE GROUP BY CNO) AVG_TABLE ON SCORE.CNO = AVG_TABLE.CNO LEFT JOIN STUDENT ON SCORE.SNO = STUDENT.SNO WHERE SCORE.DEGREE < AVG_TABLE.AVG_SCORE2 SELECT DISTINCT STUDENT.SNO ,COURSE.CNO ,G.RANK FROM STUDENT LEFT JOIN SCORE ON STUDENT.SNO = SCORE.SNO LEFT JOIN COURSE ON SCORE.CNO = COURSE.CNO LEFT JOIN GRADE ON SCORE.SNO = STUDENT.SNO CROSS JOIN GRADE G WHERE G.LOW <= SCORE.DEGREE AND G.UPP > SCORE.DEGREE
2 SELECT DISTINCT STUDENT.SNO ,COURSE.CNO ,G.RANK FROM STUDENT LEFT JOIN SCORE ON STUDENT.SNO = SCORE.SNO LEFT JOIN COURSE ON SCORE.CNO = COURSE.CNO CROSS JOIN GRADE G WHERE G.LOW <= SCORE.DEGREE AND G.UPP > SCORE.DEGREE第二个多写了一行,用这个
想多了,我只是一时想不出来了。 SELECT * FROM SCORE A WHERE A.DEGREE <(SELECT AVG(DEGREE) FROM SCORE B WHERE A.CNO=B.CNO ) 还是谢谢
1
SELECT * FROM SCORE
LEFT JOIN
(SELECT
CNO
,ROUND(AVG(DEGREE),2) AS AVG_SCORE
FROM
SCORE
GROUP BY CNO) AVG_TABLE ON SCORE.CNO = AVG_TABLE.CNO
LEFT JOIN STUDENT ON SCORE.SNO = STUDENT.SNO
WHERE SCORE.DEGREE < AVG_TABLE.AVG_SCORE2
SELECT DISTINCT
STUDENT.SNO
,COURSE.CNO
,G.RANK
FROM STUDENT
LEFT JOIN SCORE ON STUDENT.SNO = SCORE.SNO
LEFT JOIN COURSE ON SCORE.CNO = COURSE.CNO
LEFT JOIN GRADE ON SCORE.SNO = STUDENT.SNO
CROSS JOIN GRADE G
WHERE G.LOW <= SCORE.DEGREE AND G.UPP > SCORE.DEGREE
2
SELECT DISTINCT
STUDENT.SNO
,COURSE.CNO
,G.RANK
FROM STUDENT
LEFT JOIN SCORE ON STUDENT.SNO = SCORE.SNO
LEFT JOIN COURSE ON SCORE.CNO = COURSE.CNO
CROSS JOIN GRADE G
WHERE G.LOW <= SCORE.DEGREE AND G.UPP > SCORE.DEGREE第二个多写了一行,用这个
SELECT * FROM SCORE A WHERE A.DEGREE <(SELECT AVG(DEGREE) FROM SCORE B WHERE A.CNO=B.CNO )
还是谢谢