SELECT 成绩.id, 成绩.sno, 成绩.cno, 成绩.degree
FROM 成绩
WHERE (((成绩.sno) In (SELECT 成绩.sno FROM 成绩 WHERE (((成绩.cno)="3-245")))) AND ((成绩.cno)="3-105") AND ((成绩.degree)>(SELECT Max(成绩.degree) AS degree之最大值 FROM 成绩 GROUP BY 成绩.cno HAVING (((成绩.cno)="3-245")))))
ORDER BY 成绩.degree DESC;
没注意题中有这个条件，所以结果错误
加了In (SELECT 成绩.sno FROM 成绩 WHERE (((成绩.cno)="3-245"))))
SNO=107这条记录去掉了
你好，SNO=109 这个同时选择了3-105和3-245 并且3-105的成绩比3-245高，但是没有筛选出来
SELECT 成绩.id, 成绩.sno, 成绩.cno, 成绩.degree
FROM 成绩
WHERE (((成绩.sno) In (SELECT 成绩.sno FROM 成绩 WHERE (((成绩.cno)="3-245")))) AND ((成绩.cno)="3-105"))
ORDER BY 成绩.degree DESC;
查询1:
TRANSFORM Sum(SCORE.DEGREE) AS DEGREE之总计
SELECT SCORE.SNO
FROM SCORE
GROUP BY SCORE.SNO
PIVOT SCORE.CNO
查询2:
SELECT 查询1.SNO, 查询1.[3-105], 查询1.[3-245]
FROM 查询1
WHERE (((查询1.[3-105])>0) AND ((查询1.[3-245])>0) AND (([3-105]-[3-245])>=0));
查询2就是结果,不过是交叉表的形式体现数据
SELECT SCORE.*
FROM SCORE,(SELECT SNO, DEGREE FROM SCORE WHERE CNO="3-105") as [3-105],(SELECT SNO, DEGREE FROM SCORE WHERE CNO="3-245") as [3-245]
where [score].[sno]=[3-105].[sno] and score.sno=[3-245].[sno] and [3-105].[DEGREE]>[3-245].[DEGREE];
总记录：7篇 页次：1/1 9 1 :
