一道 SQL 面试题_求职面试_非技术区_程序员俱乐部

这是我在2002年左右,给公司出的一道SQL面试大题,前10(1~10)问,允许应考者充分准备不必现场作答,
后3(11~13)问要求应考者现场作答。当年被面试的人,对题目褒贬不一。

作为命题者(准确的说其中一些题目应该是我从一些经典的SQL考试题派生出来的),我的初衷还是主要考察
关系运算、集合运算相关的SQL查询语法知识的,能轻松应答的人,我个人认为,应该算是一个颇有"SQL查询"编写经验,
或者说熟练掌握了"SQL DML语法"的开发人员了。
,然而在实际工作中,虽然"一句蔚为壮观的SQL" 实现了 "功能",但是这 "一句SQL" 究竟执行的 "性能" 如何呢?
我想提醒读者,这些题目的答案不一定适用在实际的工作中。

一些参考答案及分析过几天贴出来。

有兴趣的网友可以在线回复作答一下，讨论一下

二维关系表 T(F1,F2,F3,F4,F5,F6,F7) 表示如下关系:
┌─────┬─────┬─────┬─────┬─────┬─────┬─────┐
│  学生ID  │ 学生姓名 │  课程ID  │ 课程名称 │   成绩   │  教师ID  │ 教师姓名 │
├─────┼─────┼─────┼─────┼─────┼─────┼─────┤
│    S3    │   王五   │    K4    │   政治   │    53    │    T4    │  赵老师  │
├─────┼─────┼─────┼─────┼─────┼─────┼─────┤
│    S1    │   张三   │    K1    │   数学   │    61    │    T1    │  张老师  │
├─────┼─────┼─────┼─────┼─────┼─────┼─────┤
│    S2    │   李四   │    K3    │   英语   │    88    │    T3    │  李老师  │
├─────┼─────┼─────┼─────┼─────┼─────┼─────┤
│    S1    │   张三   │    K4    │   政治   │    77    │    T4    │  赵老师  │
├─────┼─────┼─────┼─────┼─────┼─────┼─────┤
│    S2    │   李四   │    K4    │   政治   │    67    │    T5    │  周老师  │
├─────┼─────┼─────┼─────┼─────┼─────┼─────┤
│    S3    │   王五   │    K2    │   语文   │    90    │    T2    │  王老师  │
├─────┼─────┼─────┼─────┼─────┼─────┼─────┤
│    S3    │   王五   │    K1    │   数学   │    55    │    T1    │  张老师  │
├─────┼─────┼─────┼─────┼─────┼─────┼─────┤
│    S1    │   张三   │    K2    │   语文   │    81    │    T2    │  王老师  │
├─────┼─────┼─────┼─────┼─────┼─────┼─────┤
│    S4    │   赵六   │    K2    │   语文   │    59    │    T1    │  王老师  │
├─────┼─────┼─────┼─────┼─────┼─────┼─────┤
│    S1    │   张三   │    K3    │   英语   │    37    │    T3    │  李老师  │
├─────┼─────┼─────┼─────┼─────┼─────┼─────┤
│    S2    │   李四   │    K1    │   数学   │    81    │    T1    │  张老师  │
├─────┼─────┼─────┼─────┼─────┼─────┼─────┤
│   ....   │          │          │          │          │          │          │
├─────┼─────┼─────┼─────┼─────┼─────┼─────┤
│   ....   │          │          │          │          │          │          │
└─────┴─────┴─────┴─────┴─────┴─────┴─────┘

请以一句 T-SQL (Ms SQL Server 2000) 或 Jet SQL (Ms Access) 作答!
1.如果 T 表还有一字段 F0 数据类型为自动增量整型(唯一,不会重复),
 而且 T 表中含有除 F0 字段外,请删除其它字段完全相同的重复多余的脏记录数据(要保留其中的一条):

2.查询列印各科成绩最高和最低的记录: (就是各门课程的最高、最低分的学生和老师)
 课程ID,课程名称,最高分,学生ID,学生姓名,教师ID,教师姓名,最低分,学生ID,学生姓名,教师ID,教师姓名

3.按成绩从高到低顺序,查询列印所有学生四门(数学,语文,英语,政治)课程成绩: (就是每个学生的四门课程的成绩单)
 学生ID,学生姓名,数学,语文,英语,政治,有效课程数,有效平均分
 (注: 有效课程即在 T 表中有该学生的成绩记录,如不明白可不列印"有效课程数"和"有效平均分")

4.查询列印数学成绩第 10 名到第 15 名的学生成绩单
 或列印平均成绩第 10 名到第 15 名的学生成绩单
 [学生ID],[学生姓名],数学,语文,英语,政治,平均成绩

5.按各科不及格率的百分数从低到高和平均成绩从高到低顺序,查询统计列印各科平均成绩和不及格率的百分数(用"N行"表示): (就是分析哪门课程难)
 课程ID,课程名称,平均成绩,及格百分数

6.查询列印四门课程平均成绩和及格率的百分数(用"1行4列"表示): (就是分析哪门课程难)
 数学平均分,数学及格百分数,语文平均分,语文及格百分数,英语平均分,英语及格百分数,政治平均分,政治及格百分数

7.按不同老师所教不同课程平均分从高到低,查询列印: (就是分析哪个老师的哪个课程水平高)
 教师ID,教师姓名,课程ID,课程名称,平均分 (平均分按去掉一个最高分和一个最低分后取)

8.查询统计列印各科成绩,各分数段人数:
 课程ID,课程名称,[100-85],[85-70],[70-60],[<60]

9.查询列印学生平均成绩及其名次

10.查询列印各科成绩前三名的记录:(不考虑成绩并列情况)
 学生ID,学生姓名,课程ID,课程名称,成绩,教师ID,教师姓名

11.查询列印参加了所有考试科目的学生

12.查询列印"至少"与学生"S3 (王五)"参加了相同考试科目的学生

13.规范化

评论：

#1楼  2009-05-03 12:31 徐少侠      

不是你出的把？
转贴的吧？

呵呵

#2楼 [楼主] 2009-05-03 12:37 Microshaoft       @徐少侠
呵呵
95%+ 原创命题

#3楼 119.134.90.* 2009-05-03 12:41 fffff 1：

delete from T where fi not in (
select max(f1)from T
group by F2,F3,F4,F5,F6,F7
)



#4楼 119.134.90.* 2009-05-03 12:49 fffff
二维关系表 T(F1,F2,F3,F4,F5,F6,F7) 表示如下关系:
┌─────┬─────┬─────┬─────┬─────┬─────┬─────┐
│ 学生ID │ 学生姓名 │ 课程ID │ 课程名称 │ 成绩 │ 教师ID │ 教师姓名 │
8.查询统计列印各科成绩,各分数段人数:
课程ID,课程名称,[100-85],[85-70],[70-60],[<60]

select F3, F4
,sum([100-85]) as [100-85]
,sum([85-70]) as [85-70]
,sum([70-60]) as [70-60]
,sum([<60) as [<60]
from
(
select F3
, max(F4) as F4
, case when F5>85 and FF5<100 then 1 else 0 end as [100-85]
, case when F5>70 and FF5<85 then 1 else 0 end as [85-70]
, case when F5>60 and FF5<70 then 1 else 0 end as [70-60]
, case when F5<60 then 1 else 0 end as [70-60]
From T
) a

#5楼 119.134.90.* 2009-05-03 12:52 fffff --引用--------------------------------------------------
fffff:
二维关系表 T(F1,F2,F3,F4,F5,F6,F7) 表示如下关系:
┌─────┬─────┬─────┬─────┬─────┬─────┬─────┐
│ 学生ID │ 学生姓名 │ 课程ID │ 课程名称 │ 成绩 │ 教师ID │ 教师姓名 │
8.查询统计列印各科成绩,各分数段人数:
课程ID,课程名称,[100-85],[85-70],[70-60],[<60]

select F3, F4
,sum([100-85]) as [100-85]
,sum([85-70]) as [85-70]
,sum([70-60]) as [70-60]
,sum([<60) as [<60]
from
(
select F3
, max(F4) as F4
, case when F5>85 and FF5<100 then 1 else 0 end as [100-85]
, case when F5>70 and FF5<85 then 1 else 0 end as [85-70]
, case when F5>60 and FF5<70 then 1 else 0 end as [70-60]
, case when F5<60 then 1 else 0 end as [70-60]
From T
) a
--------------------------------------------------------
好像有问题？没测

#6楼 119.134.90.* 2009-05-03 12:54 fffff select F3, max(F4) as F4
,sum([100-85]) as [100-85]
,sum([85-70]) as [85-70]
,sum([70-60]) as [70-60]
,sum([<60) as [<60]
from
(
select F3
, F4
, case when F5>85 and FF5<100 then 1 else 0 end as [100-85]
, case when F5>70 and FF5<85 then 1 else 0 end as [85-70]
, case when F5>60 and FF5<70 then 1 else 0 end as [70-60]
, case when F5<60 then 1 else 0 end as [70-60]
From T
) a group by F3

这样吧。。

#7楼  2009-05-03 12:56 DiryBoy       惭愧，要求现场做的题目只有第11题看懂了，但不会做。第13题只知道将ID跟姓名（名称）放到一起不妥。面壁去了。

#8楼 [楼主] 2009-05-03 12:57 Microshaoft       @fffff
差不多
可以直接sum(case when ...) 而不必使用派生表

#9楼 116.4.55.* 2009-05-03 14:19 Garnett_KG 1.
DELETE T a
WHERE EXISTS(
SELECT * FROM T
WHERE CHECKSUM(F1,F2,F3,F4,F5,F6,F7)
=
CHECKSUM(a.F1,a.F2,a.F3,a.F4,a.F5,a.F6,a.F7)
AND F0<a.F0
)
2.

SELECT a.F3 ,
c.F4,
a.maxF5,
c.F2,
c.F3,
c.F6,
c.F7,
a.minF5,
b.F1,
b.F2,
b.F6,
b.F7
FROM
(
SELECT F3,MIN(F5) as minF5,
MAX(F5) as maxF5
FROM T
GROUP BY F3
)a ,T b,T c
WHERE a.F3=b.F3
AND a.minF5=b.F5
AND a.F3=c.F3
AND a.maxF5=b.F5


3.
SELECT F1,F2,
数学=(SELECT TOP 1 F5 FROM T WHERE F1=a.F1 AND F4='数学'),
语文=(SELECT TOP 1 F5 FROM T WHERE F1=a.F1 AND F4='语文'),
英语=(SELECT TOP 1 F5 FROM T WHERE F1=a.F1 AND F4='英语'),
政治=(SELECT TOP 1 F5 FROM T WHERE F1=a.F1 AND F4='政治'),
有效课程数=(SELECT COUNT(*) FROM T WHERE F1=a.F1),
有效平均分=(SELECT AVG(F5) FROM T WHERE F1=a.F1)
FROM T a

11.
SELECT *
FROM T a
WHERE NOT EXISTS
(
SELECT *
FROM T b
WHERE F1='S3'
AND NOT EXISTS
(
SELECT *
FROM T
WHERE F1=a.F1
AND F3=b.F3
)
)

12.
学生表(StudentID,StudentName)
教师表(TeacherID,TeacherName)
课程表(ClassID,ClassName)
成绩表(StudentId,ClassId,Score,TeacherID)




#10楼  2009-05-03 14:59 周强       1: DELETE FROM T
WHERE F0 NOT IN(
SELECT MIN(F0)
FROM T
GROUP BY F1,F2,F3,F4,F5,F6,F7
)

2:SELECT 课程ID,课程名称,最高分=MAX(分数),最低分=MIN(分数)
FROM T
GROUP BY 课程ID,课程名称


SELECT A.课程ID,A.课程名称,A.最高分,X.学生ID,X.学生姓名,X.教师ID,X.教师姓名,A.最低分,Y.学生ID,Y.学生姓名,Y.教师ID,Y.教师姓名
FROM (
SELECT 课程ID,课程名称,最高分=MAX(分数),最低分=MIN(分数)
FROM T
GROUP BY 课程ID,课程名称
) AS A
INNER JOIN T AS X ON A.课程ID=X.课程ID AND A.最高分=X.分数
INNER JOIN T AS Y ON A.课程ID=y.课程ID AND A.最低分=y.分数


3.SELECT 学生ID,学生姓名,
MAX(CASE WHEN 课程名称='数学' THEN 分数 END) AS 数学，
MAX(CASE WHEN 课程名称='语文' THEN 分数 END) AS 语文，
MAX(CASE WHEN 课程名称='英语' THEN 分数 END) AS 英语，
MAX(CASE WHEN 课程名称='政治' THEN 分数 END) AS 政治
FROM T
GROUP BY 学生ID,学生姓名

4. SELECT TOP 6 *,平均分=(数学+语文+英语+政治)/4 --未考虑某人某科缺考的情况
FROM (
SELECT TOP 15 *
FROM (
SELECT 学生ID,学生姓名,
MAX(CASE WHEN 课程名称='数学' THEN 分数 END) AS 数学，
MAX(CASE WHEN 课程名称='语文' THEN 分数 END) AS 语文，
MAX(CASE WHEN 课程名称='英语' THEN 分数 END) AS 英语，
MAX(CASE WHEN 课程名称='政治' THEN 分数 END) AS 政治
FROM T
GROUP BY 学生ID,学生姓名
) AS A
ORDER BY 数学 DESC
) AS B
ORDER BY 数学 ASC

5.SELECT 课程ID,课程名称,平均成绩=AVG(分数),及格百分数=SUM(CASE WHEN 分数>=60 THEN 1 END)/COUNT(课程名称)
FROM T
GROUP BY 课程ID,课程名称
ORDER BY AVG(分数) DESC,SUM(CASE WHEN 分数>=60 THEN 1 END)/COUNT(课程名称) ASC

6.对第5题的结果进行行变列即可。
7.SELECT 教师ID,教师姓名,课程ID,课程名称,平均分=SUM(分数)-(MAX(分数)+MIN(分数))*2/(COUNT(课程名称)-2)
FROM T
GROUP BY 教师ID,教师姓名,课程ID,课程名称

8.已有答案

9.SELECT 学生ID，学生姓名，平均分=SUM(分数)/COUNT(学生ID)
FROM T
GROUP BY 学生ID，学生姓名
ORDER BY SUM(分数)/COUNT(学生ID) DESC
10 SELECT 学生ID，学生姓名
FROM T
GROUP BY 学生ID，学生姓名
HAVING COUNT(DISTINCT 课程)>=4

11. SELECT 学生ID，学生姓名,COUNT(课程ID)
FROM T
WHERE 课程ID IN (SELECT 课程ID FROM T WHERE 学生ID='S3')
GROUP BY 学生ID，学生姓名
HAVING COUNT(课程ID)>=(SELECT COUNT(DISTINCT 课程ID) FROM T WHERE 学生ID='S3' )


12.已有答案



#11楼  2009-05-03 15:00 Hafeyang       题目不错，你的blog内容真有意思。

#12楼  2009-05-03 15:04 周强       题目还可以，基本可以考察中下级数据库程序员的水平。

#13楼  2009-05-03 16:07 风海迷沙       我一般不会在实际应用中写一句复杂逻辑的SQL语句，一是可维护性问题，二是执行效率问题，至少会用SP写，我的数据量都很大，有时都是异步统计，多做一个统计表，以空间换时间：）
所以以上题目我应该是做不出来，或是不想做。。。汗

#14楼 60.0.185.* 2009-05-03 17:46 sunnyfairy       看了一下,第4 题好像没人做对啊.
如果有并列的呢,所以第10名到第15名可能有6人,也许有60人也不一定啊.
应该用 select top N percent 吧.

#15楼  2009-05-03 17:53 周强       --引用--------------------------------------------------
sunnyfairy: 看了一下,第4 题好像没人做对啊.
如果有并列的呢,所以第10名到第15名可能有6人,也许有60人也不一定啊.
应该用 select top N percent 吧.
--------------------------------------------------------


TOP N PERCENT 是没有办法解决问题的。

应该：SELECT TOP N WITH THIS FROM ....

#16楼  2009-05-03 18:34 徐少侠       @风海迷沙
--引用--------------------------------------------------
风海迷沙: 我一般不会在实际应用中写一句复杂逻辑的SQL语句，一是可维护性问题，二是执行效率问题，至少会用SP写，我的数据量都很大，有时都是异步统计，多做一个统计表，以空间换时间：）
所以以上题目我应该是做不出来，或是不想做。。。汗
--------------------------------------------------------
感觉不对

如果是考虑执行效率，那么如果是一个复杂SQL能完成的，那么用SP(不是把刚才那个SQL封装进去哦)
我觉得性能多数是比不过单个SQL的
前提是那个SQL不是垃圾SQL而是合理的进行了表连接的。

SQL引擎是专为查询优化的，合理使用引擎本身的能力多数情况下一定比自己写的程序执行效率要好，至少不会差。

如果老是对大量数据做统计，可以考虑使用cangku.html" target="_blank">数据仓库了。

#17楼  2009-05-03 18:54 风吹柳絮非       明天去公司做做看~

#18楼  2009-05-03 19:23 风海迷沙       所以说嘛，你给出的这个张本身就存在数据冗余和设计问题~量少一点还可以，量多的话肯定要重新设计表结构，所以更是不想做。。。汗。。。

#19楼 [楼主] 2009-05-03 21:28 Microshaoft       @风海迷沙
不知道你做过类似数据迁移的工作吗？
从一些“非规范化”的数据，例如：报表中提炼规范化数据
有些类似OLAP的ETL的过程
本题的最后一问才是规范化

其实你规范化完了，或者题目即使不要求查询结果有名称、姓名等字段，这些题难度其实并没有明显降低

冗余 有可能是为了 提高效率
冗余不是贬义词

#20楼 218.81.133.* 2009-05-03 21:44 disadansa 不管是子查询还是外部查询只要用到了SUM，其他不SUM查询的列是要出现在GROUP BY中的啊，有些回帖好像有点问题。

#21楼 [楼主] 2009-05-03 22:15 Microshaoft       各位不好意思
发帖时少了一道题
10.查询列印各科成绩前三名的记录:(不考虑成绩并列情况)
学生ID,学生姓名,课程ID,课程名称,成绩,教师ID,教师姓名

已经补上

#22楼  2009-05-04 00:31 风海迷沙       算是做过吧，有一个项目每天大概要处理500w数据的ETL，为了后续统计的方便，一般只允许在日志或报表中出现过度冗余的非规范化数据，优化数据结构以后，在基础数据的读写都使用SP进行几层封装，最终页面读取的时候都是优化过的统计数据，前端也没有任何逻辑计算，我的情况依然是很难会用到单句复杂逻辑的SQL。。。汗。。。
我也没有要否定楼主的意思，只是我个人，不会这样做，要是你面试我的话，也会被归入褒贬不一的后者，结果肯定是应聘失败吧。

#23楼 123.53.186.* 2009-05-04 02:11 孤独的冰 呵，看不懂，相信我总有一天会看懂的，呵，相信自己！

#24楼  2009-05-04 09:27 sunbird69       -- 测试库下创建测试表及数据
if object_id('T') is not null
drop table T;

--select * from T
create table T
(
StudID varchar(2),
StudName nvarchar(20),
CourID varchar(2),
CourName nvarchar(20),
Score float,
TeacID varchar(2),
TeacName nvarchar(20)
)

insert into T
select 'S6','黄七','K2','语文',98,'T2','王老师' union all
select 'S3','王五','K4','政治',53,'T4','赵老师' union all
select 'S1','张三','K1','数学',61,'T1','张老师' union all
select 'S2','李四','K3','英语',88,'T3','李老师' union all
select 'S1','张三','K4','政治',77,'T4','赵老师' union all
select 'S2','李四','K4','政治',67,'T5','周老师' union all
select 'S3','王五','K2','语文',90,'T2','王老师' union all
select 'S3','王五','K1','数学',55,'T1','张老师' union all
select 'S1','张三','K2','语文',81,'T2','王老师' union all
select 'S4','赵六','K2','语文',59,'T2','王老师' union all
select 'S1','张三','K3','英语',37,'T3','李老师' union all
select 'S2','李四','K1','数学',81,'T1','张老师'

1.
delete from T
where F0 <>
(
select max(F0)
from T
group by F1,F2,F3,F4,F5,F6,F7
)

2.
select
CourID,
CourName,
Score,
StudID,
StudName,
TeacID,
TeacName
from T
where Score =
(
select max(Score)
from T as T2
where T2.CourID = T.CourID
)
union all
select
CourID,
CourName,
Score,
StudID,
StudName,
TeacID,
TeacName
from T
where Score =
(
select min(Score)
from T as T2
where T2.CourID = T.CourID
)
order by CourID

3.
select
StudID,
StudName,
'数学' = sum(case CourID when 'K1' then Score else 0 end),
'语文' = sum(case CourID when 'K2' then Score else 0 end),
'英语' = sum(case CourID when 'K3' then Score else 0 end),
'政治' = sum(case CourID when 'K4' then Score else 0 end),
'有效课程数' = count(CourID),
'有效平均分' = sum(Score) / count(CourID)
from T
group by StudID,StudName
order by StudID

4.
select T4.* from
(
select
StudID,
StudName,
'数学' = sum(case CourID when 'K1' then Score else 0 end),
'语文' = sum(case CourID when 'K2' then Score else 0 end),
'英语' = sum(case CourID when 'K3' then Score else 0 end),
'政治' = sum(case CourID when 'K4' then Score else 0 end),
'有效课程数' = count(CourID),
'有效平均分' = avg(Score)
from T
group by StudID,StudName
) as T4
where T4.数学 between (
select max(T1.Score)
from (
select top 10 Score
from T
where CourID = 'K1'
order by Score
) as T1
)
and (
select max(T2.Score)
from (
select top 15 Score
from T
where CourID = 'K1'
order by Score
) as T2
)

5.
select CourID,
CourName,
'平均成绩' = avg(Score),
'及格百分数' = cast((select count(1) from T as T5 where T5.CourID = T.CourID and Score >= 60) as float)/cast((select count(1) from T as T55 where T55.CourID = T.CourID) as float)
from T
group by CourID,CourName
order by 及格百分数 asc,平均成绩 desc

6.
select '数学' = max(case CourID when 'K1' then cast(平均分 as varchar(10)) + ',' + cast(及格百分比 as varchar(10)) end),
'语文' = max(case CourID when 'K2' then cast(平均分 as nvarchar(10)) + ',' + cast(及格百分比 as nvarchar(10)) end),
'英语' = max(case CourID when 'K3' then cast(平均分 as nvarchar(10)) + ',' + cast(及格百分比 as nvarchar(10)) end),
'政治' = max(case CourID when 'K4' then cast(平均分 as nvarchar(10)) + ',' + cast(及格百分比 as nvarchar(10)) end)
from (
select CourID,CourName,avg(score) as '平均分',
cast((select count(1) from T as T6 where T6.CourID = T.CourID and Score >= 60) as float)/
cast((select count(1) from T as T6 where T6.CourID = T.CourID) as float) as '及格百分比'
from T
group by CourID,CourName
) T66

7.
select TeacID,TeacName,CourID,CourName,(sum(Score) - max(Score) - min(Score)) / (count(Score) - 2)
from T
group by TeacID,TeacName,CourID,CourName

8.
select Courid, CourName,
sum([100-85]) as [100-85],
sum([85-70]) as [85-70],
sum([70-60]) as [70-60],
sum([<60]) as [<60]
from
(
select CourID,CourName,
case when Score>85 and Score<100 then 1 else 0 end as [100-85],
case when Score>70 and Score<85 then 1 else 0 end as [85-70],
case when Score>60 and Score<70 then 1 else 0 end as [70-60],
case when Score<60 then 1 else 0 end as [<60]
from T
) T8
group by CourID,CourName

9.
select StudID,StudName,avg(Score) as '平均成绩'
from T
group by StudID,StudName
order by 平均成绩 desc

10.
select StudID,StudName,CourID,CourName,Score,TeacID,TeacName
from T
group by CourID,CourName,Score,TeacID,TeacName,StudID,StudName
having Score >= (
select min(Score) from
(
select top 3 Score
from T as T10
where T10.CourID = T.CourID
order by Score desc
) as T102)
order by CourID,Score desc

11.
select StudID,StudName
from T
group by StudID,StudName
having count(CourID) = 4

12.
select distinct StudID,StudName
from T T1
where not exists
(
select *
from T T2
where StudID = 'S3'
and not exists
(
select *
from T
where StudID = T1.StudID
and CourID = T2.CourID
)
)

13.
学生表(StudID,StudName)
教师表(TeacID,TeacName)
课程表(CourID,CourName)
成绩表(StudId,CourId,Score,TeacID)

-- 没详细测试及考虑效率

#25楼  2009-05-04 10:17 sunbird69       -- 修改一下第四题 及 第七题
4.
select T4.* from
(
select
StudID,
StudName,
'数学' = sum(case CourID when 'K1' then Score else 0 end),
'语文' = sum(case CourID when 'K2' then Score else 0 end),
'英语' = sum(case CourID when 'K3' then Score else 0 end),
'政治' = sum(case CourID when 'K4' then Score else 0 end),
'有效课程数' = count(CourID),
'有效平均分' = avg(Score)
from T
group by StudID,StudName
) as T4
where T4.数学 between (
select min(T1.Score)
from (
select top 16 Score
from T
where CourID = 'K1'
order by Score desc
) as T1
)
and (
select min(T2.Score)
from (
select top 10 Score
from T
where CourID = 'K1'
order by Score desc
) as T2
)

7.
-- 添加考生数少于等于2时的处理,即不去最高分最低分
select TeacID,TeacName,CourID,CourName, '平均分' = (sum(Score) - max(Score) - min(Score)) / (count(Score) - 2)
from T
group by TeacID,TeacName,CourID,CourName
having count(Score) > 2
union all
select TeacID,TeacName,CourID,CourName,'平均分' = avg(Score)
from T
group by TeacID,TeacName,CourID,CourName
having count(Score) <= 2

#26楼 121.15.13.* 2009-05-05 10:02 zsm
select top 15 学生姓名 , avg(成绩) as cj from T where not exists
(

select 学生姓名 from
(
select top 10 学生姓名 , avg(成绩) as cj from t group by 学生姓名
order by cj desc

) c where c.学生姓名= T.学生姓名

) group by 学生姓名 order by cj desc

本文来自：http://www.cnblogs.com/Microshaoft/archive/2009/05/03/1448085.html