笔试的时候是看一张成绩表，包括姓名，课程，成绩，学号。三个问题，第一，查询某科成绩大于80分的学生。二，学生成绩统计并排名。三，展示一个表格，增加一列，展示每一个学生低于60分就算不及格的，高于60算及格。
具体题目忘记了，从网上找了个相似的练习下。工作上用的比较多的是查询，其它的毕竟好几年没复习了，早就忘了。
成绩表数据如下：
考察聚合函数sum()，用于返回数值列的总数（总额）。。group by 分组（配合聚合函数使用），排序用order by，默认从小到大，加个desc，从大到小。没有where子句查询所有记录
select name,SUM(score),stid from dbo.stuscoregroup by name,stid order by SUM(score) desc;运行结果（一块复制进来了）（Navicat命令列界面，输入sql语句按回车enter键）
mysql> select name,SUM(score),stid from dbo.stuscoregroup by name,stid order by SUM(score) desc;+------+------------+------+| name | SUM(score) | stid |+------+------------+------+| 张三 | 218|1 || 李四 | 200|2 || 王五 | 191|3 |+------+------------+------+3 rows in set (0.03 sec) 二、查询每个人单科最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)考察派生表（需要别名），max()函数，用于返回一列中的最大值，NULL 值不包括在计算中。先查学号与最高成绩，再把学号，姓名，课程，最高成绩查出来
select table1.stid,table1.name,table1.subject,table1.score from stuscore table1,(select stid,max(score) as maxscore from stuscore group by stid) table2 where table1.stid=table2.stid and table1.score=table2.maxscore;先查括号里面的
mysql> select stid,max(score) as maxscore from stuscore group by stid;+------+----------+| stid | maxscore |+------+----------+|1 |89 ||2 |80 ||3 |91 |+------+----------+3 rows in set (0.03 sec)结果，再把相关信息列出来
mysql> select table1.stid,table1.name,table1.subject,table1.score from stuscore table1,(select stid,max(score) as maxscore from stuscore group by stid) table2 where table1.stid=table2.stid and table1.score=table2.maxscore;+------+------+---------+-------+| stid | name | subject | score |+------+------+---------+-------+|1 | 张三 | 数学|89 ||2 | 李四 | 英语|80 ||3 | 王五 | 数学|91 |+------+------+---------+-------+3 rows in set (0.04 sec) 三、查询每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）考察avg()函数，用于返回数值列的平均值，NULL 值不包括在计算中。和分组group by用法
select name,stid,avg(score) from dbo.stuscoregroup by name,stid;运行结果
mysql> select name,stid,avg(score) from dbo.stuscoregroup by name,stid;+------+------+------------+| name | stid | avg(score) |+------+------+------------+| 张三 |1 | 72.6667|| 李四 |2 | 66.6667|| 王五 |3 | 63.6667|+------+------+------------+3 rows in set (0.04 sec) 四、查询各门课程成绩最好的学生(要求显示字段: 学号，姓名，科目，成绩)类似第二题。第一步查询把学号 换成科目
select table1.stid,table1.name,table1.subject,table1.score from stuscore table1,(select subject,max(score) as maxscore from stuscore group by subject) table2 where table1.subject=table2.subject and table1.score=table2.maxscore;运行结果
mysql> select table1.stid,table1.name,table1.subject,table1.score from stuscore table1,(select subject,max(score) as maxscore from stuscore group by subject) table2 where table1.subject=table2.subject and table1.score=table2.maxscore;+------+------+---------+-------+| stid | name | subject | score |+------+------+---------+-------+|2 | 李四 | 语文|70 ||2 | 李四 | 英语|80 ||3 | 王五 | 数学|91 |+------+------+---------+-------+3 rows in set (0.04 sec) 五、查询各门课程成绩最好的两位学生(要求显示字段: 学号，姓名，科目，成绩)服了，还没找到更好的方法，将就下。按照最快的思路还没调试好，用top应该更快（找到科目相同的，成绩的，前两列）
SELECT t1.name,t1.SUBJECT,t1.score FROM stuscore t1 WHERE(SELECT COUNT(*) FROM stuscore t2 WHERE t1.SUBJECT=t2.SUBJECT AND t2.score>=t1.score) SELECT t1.name,t1.SUBJECT,t1.score FROM stuscore t1 WHERE(SELECT COUNT(*) FROM stuscore t2 WHEREt1.SUBJECT=t2.SUBJECT AND t2.score>=t1.score) select stid 学号,name 姓名,sum(case when subject='语文' then score else 0 end )as 语文,sum(case when subject='数学' then score else 0 end )as 数学,sum(case when subject='英语' then score else 0 end )as 英语,SUM(score)总分,avg(score)平均分 from stuscoregroup by stid,name order by 总分;+------+------+------+------+------+------+---------+| 学号 | 姓名 | 语文 | 数学 | 英语 | 总分 | 平均分 |+------+------+------+------+------+------+---------+|3 | 王五 | 60| 91| 40| 191 | 63.6667 ||2 | 李四 | 70| 50| 80| 200 | 66.6667 ||1 | 张三 | 59| 89| 70| 218 | 72.6667 |+------+------+------+------+------+------+---------+3 rows in set (0.05 sec) 七、查询各门课程的平均成绩（要求显示字段：课程，平均成绩）考察avg函数，group by 分组
select subject,avg(score) from stuscoregroup by subject;运行结果
mysql> select subject,avg(score) from stuscoregroup by subject;+---------+------------+| subject | avg(score) |+---------+------------+| 数学| 76.6667|| 英语| 63.3333|| 语文| 63.0000|+---------+------------+3 rows in set (0.03 sec) 八、查询数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）考察count用法，order by排序，注意：比较的次数+1 = 排名
select stid,name,score,(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from stuscore t2 where subject='数学' order by score desc;--注释：排序，比较大小，比较的次数+1 = 排名。运行结果
mysql> select stid,name,score,(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from stuscore t2 where subject='数学' order by score desc;+------+------+-------+------+| stid | name | score | 名次 |+------+------+-------+------+|3 | 王五 |91 |1 ||1 | 张三 |89 |2 ||2 | 李四 |50 |3 |+------+------+-------+------+3 rows in set (0.13 sec) 九、查询数学成绩在2-3名的学生（要求显示字段：学号，姓名，科目，成绩）有很多种方法，下面只是其中的一种，顺着第八题做下来
select t3.* from ( select stid,name,subject,score,(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from stuscore t2 where subject='数学') t3where t3.名次 between 2 and 3 order by t3.score desc;运行结果
mysql> select t3.* from ( select stid,name,subject,score,(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from stuscore t2 where subject='数学') t3where t3.名次 between 2 and 3 order by t3.score desc;+------+------+---------+-------+------+| stid | name | subject | score | 名次 |+------+------+---------+-------+------+|1 | 张三 | 数学|89 |2 ||2 | 李四 | 数学|50 |3 |+------+------+---------+-------+------+2 rows in set (0.49 sec) 十、查询李四的数学成绩的排名方法很多，下面列出其中的一种，顺着第八题做
select stid,name,subject,score,(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from stuscore t2 where subject='数学' and name = '李四' order by score desc;运行结果
mysql> select stid,name,subject,score,(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from stuscore t2 where subject='数学' and name = '李四' order by score desc;+------+------+---------+-------+------+| stid | name | subject | score | 名次 |+------+------+---------+-------+------+|2 | 李四 | 数学|50 |3 |+------+------+---------+-------+------+1 row in set (0.05 sec)十一、查询统计如下 课程不及格（0-59）个良（60-80）个优（81-100）个
考察case when
select subject 科目,sum(case when score between 0 and 59 then 1 else 0 end) as 不及格, sum(case when score between 60 and 80 then 1 else 0 end) as 良, sum(case when score between 81 and 100 then 1 else 0 end) as 优秀 from stuscore group by subject;运行结果
mysql> select subject 科目,sum(case when score between 0 and 59 then 1 else 0 end) as 不及格, sum(case when score between 60 and 80 then 1 else 0 end) as 良, sum(case when score between 81 and 100 then 1 else 0 end) as 优秀 from stuscore group by subject;+------+--------+----+------+| 科目 | 不及格 | 良 | 优秀 |+------+--------+----+------+| 数学 | 1 | 0 | 2|| 英语 | 1 | 2 | 0|| 语文 | 1 | 2 | 0|+------+--------+----+------+3 rows in set (0.06 sec) 十二、查询统计如下数学: 张三(50分),李四(90分),王五(90分),赵六(76分)
查了下答案，先贴上
declare @s nvarchar(1000) set @s='' select @s =@s+','+name+'('+convert(nvarchar(10),score)+'分)' fromstuscore where subject='数学' set @s=stuff(@s,1,1,' ')print '数学:'+@s