如何筛选同一年度既考了语文又考了体育的学生

mxsw

请教各位大师，如何筛选同一年度既考了语文又考了体育的学生。

ly

如果你是在excel中使用，请将代码中的所有Sheet1加上$符号，如：FROM [Sheet1$]

1. SELECT B.班级, B.学号, B.姓名, B.年度, B.课程, B.考试分数
   
2. FROM (SELECT A.班级, A.学号, A.姓名, A.年度, "体育" AS 课程, A.体育 AS 考试分数
   
3. FROM (SELECT Sheet1.班级, Sheet1.学号, Sheet1.姓名, Sheet1.年度, Sum(IIf([课程]="语文",[考试分数],0)) AS 语文, Sum(IIf([课程]="体育",[考试分数],0)) AS 体育
   
4. FROM Sheet1
   
5. GROUP BY Sheet1.班级, Sheet1.学号, Sheet1.姓名, Sheet1.年度
   
6. HAVING (((Sum(IIf([课程]="语文",1,0)+IIf([课程]="体育",1,0)))=2))
   
7. )  AS A
   
8. UNION ALL SELECT A.班级, A.学号, A.姓名, A.年度, "语文" AS 课程, A.语文 AS 考试分数
   
9. FROM (SELECT Sheet1.班级, Sheet1.学号, Sheet1.姓名, Sheet1.年度, Sum(IIf([课程]="语文",[考试分数],0)) AS 语文, Sum(IIf([课程]="体育",[考试分数],0)) AS 体育
   
10. FROM Sheet1
    
11. GROUP BY Sheet1.班级, Sheet1.学号, Sheet1.姓名, Sheet1.年度
    
12. HAVING (((Sum(IIf([课程]="语文",1,0)+IIf([课程]="体育",1,0)))=2))
    
13. )  AS A
    
14. )  AS B
    
15. ORDER BY B.年度, B.学号

复制代码