- TRANSFORM First(百分比)
- select 班级,等级 from
- (select a.班级,a.科目,a.等级,format(a.d/b.s,"0.00%") as 百分比 from
- (select 班级,科目,等级,count(等级) as d from
- (select 班级,科目,Switch(t>80,"优秀",t>70,"良好",t>=60,"及格",t<60,"不及格") as 等级 from
- (select 班级,"语文" as 科目,语文 as t from [成绩表$]
- union all
- select 班级,"数学",数学 from [成绩表$]
- union all
- select 班级,"英语",英语 from [成绩表$]))
- group by 班级,科目,等级)a
- LEFT JOIN
- (select 班级,count(学生) as s from [成绩表$]group by 班级)b
- on a.班级=b.班级)
- group by 班级,等级
- PIVOT 科目
复制代码 我的答案.
PS:方法有多种,这题考的是交叉查询/子查询/联合查询/多表查询/JOIN连接等几种常用查询的结合.{:171:}
|