一起来SQL综合练练手

wuxiang_123

SQL综合练练手

wuxiang_123

自己顶一下,题目不难的

魂断蓝桥

我觉得挺难得

onthetrip

做到一半,做不下去了,搞不定那个百分比,太难了,可能思路有问题

1. select aa.班级,aa.科目,aa.成绩,switch(aa.成绩>80,"优秀",aa.成绩>70,"良好",aa.成绩>=60,"及格",aa.成绩<60,"不及格") as 等级 from (select "语文" as 科目,班级,语文 as 成绩 from [成绩表$] union all select "数学" ,班级,数学 from [成绩表$] union all select "英语" ,班级,英语 from [成绩表$])aa

复制代码

wuxiang_123

方向对了.只差一点点就成功,加油

wuxiang_123

我的答案参考:

1. TRANSFORM First(百分比)
   
2. select 班级,等级 from
   
3. (select a.班级,a.科目,a.等级,format(a.d/b.s,"0.00%") as 百分比 from
   
4. (select 班级,科目,等级,count(等级) as d from
   
5. (select 班级,科目,Switch(t>80,"优秀",t>70,"良好",t>=60,"及格",t<60,"不及格") as 等级 from
   
6. (select 班级,"语文" as 科目,语文 as t from [成绩表$]
   
7. union all
   
8. select 班级,"数学",数学 from [成绩表$]
   
9. union all
   
10. select 班级,"英语",英语 from [成绩表$]))
    
11. group by 班级,科目,等级)a
    
12. LEFT JOIN
    
13. (select 班级,count(学生) as s from [成绩表$]group by 班级)b
    
14. on a.班级=b.班级)
    
15. group by 班级,等级
    
16. PIVOT 科目

复制代码

PS:方法有多种,这题考的是交叉查询/子查询/联合查询/多表查询/JOIN连接等几种常用查询的结合.

onthetrip

好难啊,基本上把SQL的主要语句都综合进去了.学习了

b12b3bb

经  典   !

chenhh803

学习了，数据透视表也可以做得这么强

119

哈哈，会者不难，难者不会啊。