如果你是在excel中使用,请将代码中的所有Sheet1加上$符号,如:FROM [Sheet1$]
- SELECT B.班级, B.学号, B.姓名, B.年度, B.课程, B.考试分数
- FROM (SELECT A.班级, A.学号, A.姓名, A.年度, "体育" AS 课程, A.体育 AS 考试分数
- FROM (SELECT Sheet1.班级, Sheet1.学号, Sheet1.姓名, Sheet1.年度, Sum(IIf([课程]="语文",[考试分数],0)) AS 语文, Sum(IIf([课程]="体育",[考试分数],0)) AS 体育
- FROM Sheet1
- GROUP BY Sheet1.班级, Sheet1.学号, Sheet1.姓名, Sheet1.年度
- HAVING (((Sum(IIf([课程]="语文",1,0)+IIf([课程]="体育",1,0)))=2))
- ) AS A
- UNION ALL SELECT A.班级, A.学号, A.姓名, A.年度, "语文" AS 课程, A.语文 AS 考试分数
- FROM (SELECT Sheet1.班级, Sheet1.学号, Sheet1.姓名, Sheet1.年度, Sum(IIf([课程]="语文",[考试分数],0)) AS 语文, Sum(IIf([课程]="体育",[考试分数],0)) AS 体育
- FROM Sheet1
- GROUP BY Sheet1.班级, Sheet1.学号, Sheet1.姓名, Sheet1.年度
- HAVING (((Sum(IIf([课程]="语文",1,0)+IIf([课程]="体育",1,0)))=2))
- ) AS A
- ) AS B
- ORDER BY B.年度, B.学号
复制代码 |