用SQl来做学生成绩分析

hehex

小麦老师的好贴，标记下来慢慢学习。
想当初俺也跟着香帅的仙履奇缘系列认真学过一段SQL,灵活运用方面就和小麦老师差远了。
还得努力啊。

wuxiang_123

mps777 发表于 2013-5-2 09:16
SQL里好像没有这2个函数的。
是添加辅助列吗？

1. select 姓名,科目,成绩 from
   
2. (select 姓名,'语文' as 科目,语文 as 成绩,语文*100000 as 成绩2 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 语文 desc) union all
   
3. select 姓名,'数学',数学,数学*10000 as 成绩 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 数学 desc) union all
   
4. select 姓名,'英语',英语,英语*1000 as 成绩 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 英语 desc) union all
   
5. select 姓名,'物理',物理,物理*100 as 成绩 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 物理 desc) union all
   
6. select 姓名,'化学',化学,化学*10 as 成绩 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 化学 desc) union all
   
7. select 姓名,'生物',生物,生物 as 成绩 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 生物 desc) union all
   
8. select 姓名,'总分',总分,总分*10^6 as 成绩 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 总分 desc)) order by 成绩2 desc

复制代码

可改为：

1. 
   
2. select 姓名,科目,成绩 from
   
3. (select 姓名,'语文' as 科目,语文 as 成绩 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 语文 desc) union all
   
4. select 姓名,'数学',数学,数学 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 数学 desc) union all
   
5. select 姓名,'英语',英语,英语 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 英语 desc) union all
   
6. select 姓名,'物理',物理,物理 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 物理 desc) union all
   
7. select 姓名,'化学',化学,化学 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 化学 desc) union all
   
8. select 姓名,'生物',生物,生物 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 生物 desc) union all
   
9. select 姓名,'总分',总分,总分 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 总分 desc))
   
10. order by INSTR('总分语文数学英语物理化学生物',科目),成绩 DESC

复制代码

INSTR：从指定搜索位置开始，返回需要搜索的字符串在被搜索字符串中首次出现的位置（返回的是数字）
语法：
INSTR("起始搜索位置','被搜索的字符串','需要搜索的字符串')
其中，第一参数可省略，省略则默认为从头开始搜索。


同样地，也可以使用JION连接来代替子查询，提高速度。

wuxiang_123

平平淡淡的天使 发表于 2013-5-2 10:28
你看这个。。MQ做好，重新打开，才没多少时间，就弹出此对话框

这个要视情况判断。

qhllqhll

谢谢
用SQl来做学生成绩分析

平平淡淡的天使

wuxiang_123 发表于 2013-5-2 16:22
这个要视情况判断。

大师，，你觉得 我会是什么情况？这个特麻烦，筛选一下，弹出对话框。。重新做一个也是如此。。

kuangben8

强大的东西都好复杂！感谢分享。

billiechen

非常不错，值得学习一下！

2011geili

{:soso_e179:}

在和弦处等你

wuxiang_123 发表于 2013-5-2 16:19
可改为：
INSTR：从指定搜索位置开始，返回需要搜索的字符串在被搜索字符串中首次出现的位置（返回的 ...

请问香帅老师，在使用转置时如何控制行字段的顺序呢？如小麦老师的统计各分数段人数，如何使转制后的行字段可以按“语文数学英语政治”的顺序出现？谢谢。



转置语句为：

1. TRANSFORM COUNT(T.成绩)
   
2. SELECT T.班别, T.科目 FROM
   
3. (SELECT A.*, SWITCH(A.成绩<60,"<60", A.成绩<70,"60-69", A.成绩<80,"70-79", A.成绩<90,"80-89", A.成绩>=90,"90-100") AS 成绩分段 FROM
   
4. (SELECT 班别, 语文 AS 成绩, "语文" AS 科目 FROM [三班汇总$M:S] UNION ALL
   
5. SELECT 班别, 数学,"数学" FROM [三班汇总$M:S] UNION ALL
   
6. SELECT 班别, 英语,"英语" FROM [三班汇总$M:S] UNION ALL
   
7. SELECT 班别, 政治,"政治" FROM [三班汇总$M:S]) A) T
   
8. GROUP BY T.班别, T.科目
   
9. PIVOT T.成绩分段

复制代码

每天都能从小麦老师和香帅老师那学到新东西。{:soso_e121:}

wuxiang_123

在和弦处等你 发表于 2013-5-3 01:52
请问香帅老师，在使用转置时如何控制行字段的顺序呢？如小麦老师的统计各分数段人数，如何使转制后的行字 ...

1. 
   
2. TRANSFORM COUNT(T.成绩)
   
3. SELECT T.班别, T.科目 FROM
   
4. (SELECT A.*, SWITCH(A.成绩<60,"<60", A.成绩<70,"60-69", A.成绩<80,"70-79", A.成绩<90,"80-89", A.成绩>=90,"90-100") AS 成绩分段 FROM
   
5. (SELECT 班别, 语文 AS 成绩, "语文" AS 科目 FROM [三班汇总$M:S] UNION ALL
   
6. SELECT 班别, 数学,"数学" FROM [三班汇总$M:S] UNION ALL
   
7. SELECT 班别, 英语,"英语" FROM [三班汇总$M:S] UNION ALL
   
8. SELECT 班别, 政治,"政治" FROM [三班汇总$M:S]) A) T
   
9. GROUP BY T.班别, T.科目
   
10. ORDER BY T.班别,INSTR('语文数学英语政治',T.科目)
    
11. PIVOT T.成绩分段

复制代码

在转置中，ORDER BY是对行记录排序。
列字段排序在PIVOT中，如PIVOT 列字段 IN (列出需要返回的列字段，以逗号分隔，列字段可以为需要转置表中没有的字段）
注：列字段的项为转置后结果的列字段。