MSSQL教程之一道批驳纷歧的 SQL 测验题

Mysql的存储引擎接口定义良好。有兴趣的开发者可以通过阅读文档编写自己的存储引擎。
《一道批驳纷歧的SQL测验题》
playyuer命题,playyuer、ccat撰稿

信任有很多网友比来在网上见过一则名为《一道批驳纷歧的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表起名为"成就表"

1.假如T表另有一字段F数据范例为主动增量整型(独一,不会反复),
并且T表中含有除F字段外,请删除别的字段完整不异的反复过剩的脏纪录数据:

本成绩就是一个清算"逻辑反复"纪录的成绩,固然,这类情形完整能够使用主键束缚来
根绝!但是,实际情形常常是原始数据在"洗濯"后,方可平安利用,并且逻辑主键过早的
束缚,将会给收罗原始数据带来方便,比方:从刷卡机上读取考勤纪录。到了使用数据
的时分,脏数据就该被扫地出门了!之以是题中要保存一个主动标识列,是由于它切实其实
是上面谜底所必需的条件:

DELETEL
FROM"成就表"L
JOIN"成就表"R
ONL."先生ID"=R."先生ID"ANDL."课程ID"=R."课程ID"ANDL.F>R.F

这是思绪最精致且最间接无效的办法之一。用不等自连接,恰好能够将统一组反复数
据中F字段值最小的那一条留下,并选出别的的删失落,假如只要一条,天然也不会被选
中了。这里还要夸大一下,人人必定要分分明被操纵的基础表也就是DELETE关头字
后的表和过滤前提所利用的由基础表毗连而成的二维表数据集,也就是FROM子句的
全体。在自毗连的FROM子句最少要取一一般名来援用基础表。别号的利用在编写年夜
量相似布局的SQL时十分便利,并且利于一致程序机关静态SQL。若有需要增强前提,
还可持续利用WHERE子句。假如下面的例子还不敷直不雅,上面仿照一个不等自连接,
有一组数(1,2,3),作一个不等自连接,令左子集年夜于右子集,是:
21
31
32
假如如今选出左子集,就是2和3了。1在右侧没有比它更小的数据能够与之婚配,
因而被过滤了。假如数据大批反复,效力会差强者意,亏得不是SELECT,而是DELETE
无需前往了局集,影响天然小多了。

DELETET
FROM成就表T
WHEREFNOTIN(SELECTMIN(F)
FROM成就表I
GROUPBYI.先生ID,I.课程ID
HAVINGCOUNT(*)>1
)
ANDFNOTIN(SELECTMIN(F)
FROM成就表I
GROUPBYI.先生ID,I.课程ID
HAVINGCOUNT(*)=1
)

这类办法思绪很复杂,就像翻译天然言语,很准确地形貌了切合前提纪录的特征,乃至
第二个前提切实其实过剩。最少应当用一个>=号兼并这两个前提或只保存恣意一个前提,
进步效力。

DELETET
FROM成就表T
WHEREF>(SELECTMIN(F)
FROM成就表ASI
WHEREI.先生ID=T.先生ID
ANDI.课程ID=T.课程ID
GROUPBYI.先生ID,I.课程ID
)

这类办法,基础上是办法一的相干子查询版本,懂得笛卡尔积的读者能会好了解些,而
且用到了统计函数,因而效力不是太高。仔细的读者会发明子查询里的GROUPBY子
句没有需要,往失落它应当会进步一些效力的。

关于DELETE语句的调试,有履历的程序员城市先用有害的SELECT临时取代伤害的
DELETE。比方:

SELECTL.*
--DELECTL临时正文失落
FROM"成就表"L
JOIN"成就表"R
ONL."先生ID"=R."先生ID"ANDL."课程ID"=R."课程ID"ANDL.F>R.F

如许,极年夜地减小了在线数据被偶然损坏的大概性,固然数据提早备份也很主要。同理
UPDATE和INSERT写操纵也应照此行事。从数据库道理的干系运算的角度来看INSERT、
UPDATE和DELETE这些写操纵都属于典范的"选择(Selection)"运算,UPDATE和INSERT
并且仍是"投影(Projection)"运算,它们都是这些干系运算的"写"使用的体现情势。
实在,查询的目标也原本不过就是扫瞄、删除、更
新或拔出。一般写操纵也比读操纵损耗更年夜,假如索引过量,只会下降效力。

选择"子查询"仍是"毗连"在效力是有不同的,但最关头的不同仍是体现在查询的了局
集的读写性上,开辟职员在写一个"只读"使用的查询纪录集时,"子查询"和"毗连"各自
的效力就是应当起首思索的成绩,可是假如要完成"可写"使用的查询了局集,则不管是
相干仍是非相干子查询都是在庞大使用中难以免的。

以上办理计划中,应当说第一种办法,简便无效,很有创意,是值得保举的办法。固然,
最复杂的写法应当是:

DELETET
FROMT,TT1
WHERET.先生ID=T1.先生IDandT.课程ID=T.课程IDandT.F<T1.F

实在这就是办法一的"尺度"(但的确实不是《ANSI/ISOSQL》尺度)毗连写法,以下各
题谜底为了便于读者了解,一样平常不接纳这类写法,这也是《ANSI/ISOSQL》尺度所鼓
励的,JOIN的确更简单地表达表之间的干系,有乐趣的读者可自行改写。假如利用
"*="完成两表以上的外毗连时,要注重此时WHERE子句的AND前提是有按次的,尽
管《ANSI/ISOSQL》尺度不同意WHERE前提的按次影响查询了局,可是FROM子句
的各表毗连的按次能够影响查询了局。

2.列印各科成就最高和最低的相干纪录:(就是各门课程的最高、最低分的先生和先生)
课程ID,课程称号,最高分,先生ID,先生姓名,教员ID,教员姓名,最低分,先生ID,先生姓名,教员ID,教员姓名

假如这道题如果仅仅求出各科成就最高分或最低分,则是一道十分复杂的题了:

SELECTL.课程ID,MAX(L.课程称号),MAX(L.成就)AS最高分,MIN(L.成就)AS最低分
FROM成就表L
GROUPBYL.课程ID

可是,刁钻的标题倒是要列出各科最高和最低成就的相干纪录,这也常常才是真正需求。
既然已选出各科最高和最低分,那末,剩下的就是把先生和教员的信息并进这个了局
集。假如照如许写下往,十分贫苦,由于要增加的字段太多了,很快就使代码变得难于
办理。仍是换个思绪吧:

SELECTL.课程ID,L.课程称号,L.[成就]AS最高分,L.[先生ID],L.[先生姓名],L.[教员ID],L.[教员姓名]
,R.[成就]AS最低分,R.[先生ID],R.[先生姓名],R.[教员ID],R.[教员姓名]
FROM成就表L
JOIN成就表ASRONL.[课程ID]=R.[课程ID]
WHEREL.[成就]=(SELECTMAX(IL.[成就])
FROM成就表AS[IL]
WHEREL.[课程ID]=IL.[课程ID]
GROUPBYIL.[课程ID]
)
AND
R.[成就]=(SELECTMIN(IR.[成就])
FROM成就表AS[IR]
WHERER.[课程ID]=IR.[课程ID]
GROUPBYIR.[课程ID]
)

乍一看谜底,仿佛很庞大,实在假如把握了机关交织透视表的基础办法和相干子查询的
常识,成绩水到渠成。因为最低和最高分都是针对课程信息的,该谜底奇妙地把课程信
息兼并到了最高分的数据会合,固然也能够兼并到最低分中。代码中规中矩,作风很好,
可读性也是不错的。

3.按均匀成就从高到低按次,列印一切先生的四门(数学,语文,英语,政治)课程成就:(就是每一个先生的四门课程的成就单)
先生ID,先生姓名,数学,语文,英语,政治,无效课程数,无效均匀分
(注:无效课程即在T表中有该先生的成就纪录,如不分明可不列印"无效课程数"和"无效均匀分")

必要申明的是:标题之以是明白提出"四门(数学,语文,英语,政治)课程"是有事理的,
由于完成时,切实其实没法制止使原基础表中的行上的数据的值影响列,这又是一个典范的
"行变列"的相干子查询:

SELECT先生ID,MAX(先生姓名)AS先生姓名
,(SELECT成就FROM成就表WHERE先生ID=T.先生IDAND课程ID=K1)AS数学
,(SELECT成就FROM成就表WHERE先生ID=T.先生IDAND课程ID=K2)AS语文
,(SELECT成就FROM成就表WHERE先生ID=T.先生IDAND课程ID=K3)AS英语
,(SELECT成就FROM成就表WHERE先生ID=T.先生IDAND课程ID=K4)AS政治
,COUNT(*)AS无效课程数,AVG(T.成就)AS均匀成就
FROM成就表AST
GROUPBY先生ID
ORDERBY均匀成就

这能够说也是一个很礼貌的解法,在这类使用场所,子查询要比连接代码可读性强很多。
假如数据库引擎认
为把它剖析成连接更好,那就由它往吧,实在原本相干子查询也一定含有毗连。这里再增补一下,在实践使用
中假如再加一张表Ranks(Rank,MinValue,MaxValue):
┌──────────┬──────────┬──────────┐
│Rank│MinValue│MaxValue│
├──────────┼──────────┼──────────┤
│A│90│100│
├──────────┼──────────┼──────────┤
│B│89│80│
├──────────┼──────────┼──────────┤
│C│79│70│
├──────────┼──────────┼──────────┤
│D│69│60│
├──────────┼──────────┼──────────┤
│E│60│0│
└──────────┴──────────┴──────────┘

就能够完成一个十分有有用代价的使用:

select先生ID,MAX(先生姓名)as先生姓名
,(select成就fromTwhere先生ID=T0.先生IDand课程ID=K1)as数学
,(SELECTmax(Rank)
fromRanks,t
wheret.成就>=Ranks.MinValue
andt.成就<=Ranks.MaxValue
andt.先生ID=T0.先生IDandt.课程ID=K1
)as数学级别
,(select成就fromTwhere先生ID=T0.先生IDand课程ID=K2)as语文
,(SELECTmin(Rank)
fromRanks,t
wheret.成就>=Ranks.MinValue
andt.成就<=Ranks.MaxValue
andt.先生ID=T0.先生IDandt.课程ID=K2
)as语文级别
,(select成就fromTwhere先生ID=T0.先生IDand课程ID=K3)as英语
,(SELECTmax(Rank)
fromRanks,t
wheret.成就>=Ranks.MinValue
andt.成就<=Ranks.MaxValue
andt.先生ID=T0.先生IDandt.课程ID=K3
)as英语级别
,(select成就fromTwhere先生ID=T0.先生IDand课程ID=K4)as政治
,(SELECTmin(Rank)
fromRanks,t
wheret.成就>=Ranks.MinValue
andt.成就<=Ranks.MaxValue
andt.先生ID=T0.先生IDandt.课程ID=K4
)as政治级别
,count(*),avg(t0.成就)
,(SELECTmax(Rank)
fromRanks
whereAVG(T0.成就)>=Ranks.MinValue
andAVG(T0.成就)<=Ranks.MaxValue
)AS均匀级别
fromTasT0
groupby先生ID

这里外表上利用了不等毗连,再细心想一想,Ranks表中每笔记录的区间是没有交集的,
实在也能够以为是等值毗连,如许的表计划无疑存在着优秀的扩大性,假如标题只需求

列印(先生ID,先生姓名,无效课程数,无效均匀分,均匀分级别):

select先生ID,MAX(先生姓名)as先生姓名,count(*),avg(t0.成就)
,(SELECTmax(Rank)
fromRanks
whereAVG(T0.成就)>=Ranks.MinValue
andAVG(T0.成就)<=Ranks.MaxValue
)AS均匀级别
fromTasT0
groupby先生ID

则如许的办理计划就对照周全了。

回到原题,再先容一个对照取巧的举措,仅需一个复杂分组查询便可办理成绩,有履历的读者大概已想到了
,那就是CASE:

SELECT先生ID,MIN(先生姓名)
,SUM(CASE课程IDWHENK1THEN成就ELSE0END)AS数学
,SUM(CASE课程IDWHENK2THEN成就ELSE0END)AS语文
,SUM(CASE课程IDWHENK3THEN成就ELSE0END)AS英语
,SUM(CASE课程IDWHENK4THEN成就ELSE0END)AS政治
,COUNT(*)AS无效课程数,AVG(T.成就)AS均匀成就
FROM成就表AST
GROUPBY先生ID
ORDERBY均匀成就DESC

固然大概初看谜底感到有点怪,实在很好了解,可读性其实不低,效力也很高。但它不克不及
像前一个谜底那样,在成就中辨别出某一门课这个先生事实是缺考(NULL),仍是真得
零分。这个解法充实使用了CASE语句举行数据分类的感化:CASE将成就按课程分
成四类,SUM用来消往过剩的0。

SELECT[T].[先生ID],MAX([T].[先生姓名])AS先生姓名
,MAX([T1].[成就])AS数学,MAX([T2].[成就])AS语文,MAX([T3].[成就])AS英语,MAX([T4].[成就])AS政治,COUNT([T].[课程ID])AS无效课程数
,(ISNULL(MAX([T1].[成就]),0)+ISNULL(MAX([T2].[成就]),0)+ISNULL(MAX([T3].[成就]),0)+ISNULL(MAX([T4].[成就]),0))/COUNT([T].[课程ID])AS无效均匀分
FROM成就表T
LEFTJOIN成就表AS[T1]
ON[T].[先生ID]=[T1].[先生ID]AND[T1].[课程ID]=K1
LEFTJOIN成就表AS[T2]
ON[T].[先生ID]=[T2].[先生ID]AND[T2].[课程ID]=K2
LEFTJOIN成就表AS[T3]
ON[T].[先生ID]=[T3].[先生ID]AND[T3].[课程ID]=K3
LEFTJOIN成就表AS[T4]
ON[T].[先生ID]=[T4].[先生ID]AND[T4].[课程ID]=K4
GROUPBY[T].[先生ID]
ORDERBY无效均匀分DESC

这个办法是相称正统的连接解法,只管写起来贫苦了些,但仍是不难了解的。再从有用
角度思索一下,实在需求常常不是象本题明白提出"列印四门(数学,语文,英语,政治)
课程"如许的绝对静态的需求,该是静态SQL大显神通的时分了,很分明办法一的写法
无疑是使用程序机关静态SQL的最好选择,固然另两个SQL纪律仍是挺分明的,一样
不难机关。以CASE版谜底为例:先用一个游标遍历,掏出一切课程凑成:
SUM(CASE课程IDWHEN课程称号THEN成就ELSE0END)AS课程称号情势,
再补上SELECT和FROM、WHERE等需要前提,一个天生静态成就单的SQL就出生了,
只需再由相干程序挪用实行便可,如许就能够算一个更完美的办理计划了。

实在,最相似的典范使用是在主、细干系中的主表投影中完成细表的汇总统计行,
比方两张表:
Master(F,f1,f2...)一对多Details(F,f3,f4...)
SELECT*
,(SELECTCOUNT(*)
FROMDetails
WHEREMaster.F=Details.F
)
,(SELECTSUM(F3)
FROMDetails
WHEREMaster.F=Details.F
)
FROMMaster

4.按各科不屈均成就从低到高和合格率的百分数从高到低按次,统计并列印各科均匀成就和不合格率的百分数(用"N行"暗示):(就是剖析哪门课程难)
课程ID,课程称号,均匀成就,合格百分比

SELECT课程ID,MAX(课程称号)AS课程称号,AVG(成就)AS均匀成就
,100*SUM(CASEWHEN成就>=60THEN1ELSE0END)/COUNT(*)AS合格百分数
FROM成就表T
GROUPBY课程ID
ORDERBY合格百分比DESC

这道题应当说是算复杂的了,就是用"行"来供应体现情势的。只需想分明要对数据如
何分组,取统计会萃函数,就高枕无忧了。

5.列印四门课程均匀成就和合格率的百分数(用"1行4列"暗示):(就是剖析哪门课程难)
数学均匀分,数学合格百分数,语文均匀分,语文合格百分数,英语均匀分,英语合格百分数,政治均匀分,政治合格百分数

这道题实在就是上一题的"列"体现情势版本,相对上一题,本题是静态的,由于本题
同第三题一样使用行上的数据机关了列,要完成扩大必需再使用别的的程序机关静态
SQL:

SELECTSUM(CASEWHEN课程ID=K1THEN成就ELSE0END)/SUM(CASE课程IDWHENK1THEN1ELSE0END)AS数学均匀分
,100*SUM(CASEWHEN课程ID=K1AND成就>=60THEN1ELSE0END)/SUM(CASEWHEN课程ID=K1THEN1ELSE0END)AS数学合格百分数
,SUM(CASEWHEN课程ID=K2THEN成就ELSE0END)/SUM(CASE课程IDWHENK2THEN1ELSE0END)AS语文均匀分
,100*SUM(CASEWHEN课程ID=K2AND成就>=60THEN1ELSE0END)/SUM(CASEWHEN课程ID=K2THEN1ELSE0END)AS语文合格百分数
,SUM(CASEWHEN课程ID=K3THEN成就ELSE0END)/SUM(CASE课程IDWHENK3THEN1ELSE0END)AS英语均匀分
,100*SUM(CASEWHEN课程ID=K3AND成就>=60THEN1ELSE0END)/SUM(CASEWHEN课程ID=K3THEN1ELSE0END)AS英语合格百分数
,SUM(CASEWHEN课程ID=K4THEN成就ELSE0END)/SUM(CASE课程IDWHENK4THEN1ELSE0END)AS政治均匀分
,100*SUM(CASEWHEN课程ID=K4AND成就>=60THEN1ELSE0END)/SUM(CASEWHEN课程ID=K4THEN1ELSE0END)AS政治合格百分数
FROM成就表T

这一句看起来很长,但实践上是最典范的CASE使用,很有用的数据剖析手艺。先将原
表中的成就一列一连投影8次备用于四门分歧课程,充实使用CASE和数据的值域
[k1,k2,k3,k4]来分别数据,再使用SUM()[1+...+1]完成了看似原本应
该用COUNT(*)的计数器的功效,这内里不要说连接和子查询,乃至连Groupby分组
的陈迹都找不到!假如读起来费劲,完整能够先只保存一个字段,绝对好了解些,看懂后
一一补全。本题也能够算一个"行变列"的交织透视暗示例吧!别的,"行"相对"列"
是静态的,"行"是绝对无穷的,"列"是绝对无限的,"行"的增删是使用级的,可"随便"增
删,"列"的增删是办理级的,不要容易变化!

6.按分歧先生所教分歧课程均匀分从高到低列印:(就是剖析哪一个先生的哪一个课程程度高)
教员ID,教员姓名,课程ID,课程称号,均匀分

SELECT教员ID,MAX(教员姓名)AS教员姓名,课程ID,MAX(课程称号)AS课程称号,AVG(成就)AS均匀成就
FROM成就表T
GROUPBY课程ID,教员ID
ORDERBYAVG(成就)DESC

这道题切实其实没啥好说的,就算闭着眼,不下手,谜底也应信口开河!
假如均匀分按往失落一个最高分和一个最低分后获得,则也不难写出:

SELECT教员ID,MAX(教员姓名),课程ID,MAX(课程称号)AS课程称号--,AVG(成就)AS均匀成就
,(SUM(成就)
-(SELECTMAX(成就)
FROM成就表
WHERE课程ID=T1.课程IDAND教员ID=T1.教员ID)
-(SELECTMIN(成就)
FROM成就表
WHERE课程ID=T1.课程IDand教员ID=T1.教员ID))
/CAST((SELECTCOUNT(*)-2
FROM成就表
WHERE课程ID=T1.课程IDAND教员ID=T1.教员ID)ASFLOAT)AS均匀分
FROM成就表AST1
WHERE(SELECTCOUNT(*)-2
FROM成就表
WHERE课程ID=T1.课程IDAND教员ID=T1.教员ID)>0
GROUPBY课程ID,教员ID
ORDERBY均匀分DESC

7.列印数学成就第10名到第15名的先生成就单
或列印均匀成就第10名到第15名的先生成就单
[先生ID],[先生姓名],数学,语文,英语,政治,均匀成就

假如只思索一门课程,如:数学成就,十分复杂:
selectTop5*
fromT
where课程id=K1
and成就notin(selecttop15成就
fromT
orderby成就desc
)
orderby成就desc

select*
fromT
where课程id=K1
and成就notin(selecttop10成就
fromT
orderby成就desc
)
and成就in(selecttop15成就
fromT
orderby成就desc
)
orderby成就desc

从逻辑上说,第10名到第15名就是从原前15名,"再"挑出前5名不要,保存剩下
的5名。第二种写法是夙昔15名里挑出不属于原前10名的纪录,把两个数据集做
一个差,因而要多用一个
子查询,效力绝对较低,它,假如要有《ANSI/ISOSQL》的EXCEPT
关头字就是最幻想的了。

这类技能在数据"分页"的使用中常常使用,只需遵守以下准绳便可:

SELECTTop@PageSize*
FROMT
WHERESortFieldNOTIN(SELECTTOP@PageSize*@PageiSortField
FROMT
ORDERBYSortField
)
ORDERBYSortField

至此,该题考查的次要目标已到达。至于列印了了成就单:
[先生ID],[先生姓名],数学,语文,英语,政治,均匀成就后面也有相似的标题,做起来
的确贫苦,因而上面仅供应参考谜底,就不赘述了:

SELECTDISTINCTtop5
[成就表].[先生ID],
[成就表].[先生姓名]AS先生姓名,
[T1].[成就]AS数学,
[T2].[成就]AS语文,
[T3].[成就]AS英语,
[T4].[成就]AS政治,
ISNULL([T1].[成就],0)+ISNULL([T2].[成就],0)+ISNULL([T3].[成就],0)+ISNULL([T4].[成就],0)as总分
FROM[成就表]
LEFTJOIN[成就表]AS[T1]
ON[成就表].[先生ID]=[T1].[先生ID]AND[T1].[课程ID]=k1
LEFTJOIN[成就表]AS[T2]
ON[成就表].[先生ID]=[T2].[先生ID]AND[T2].[课程ID]=k2
LEFTJOIN[成就表]AS[T3]
ON[成就表].[先生ID]=[T3].[先生ID]AND[T3].[课程ID]=k3
LEFTJOIN[成就表]AS[T4]
ON[成就表].[先生ID]=[T4].[先生ID]AND[T4].[课程ID]=k4
WHEREISNULL([T1].[成就],0)+ISNULL([T2].[成就],0)+ISNULL([T3].[成就],0)+ISNULL([T4].[成就],0)
NOTIN
(SELECT
DISTINCT
TOP15WITHTIES
ISNULL([T1].[成就],0)+ISNULL([T2].[成就],0)+ISNULL([T3].[成就],0)+ISNULL([T4].[成就],0)
FROM[成就表]
LEFTJOIN[成就表]AS[T1]
ON[成就表].[先生ID]=[T1].[先生ID]AND[T1].[课程ID]=k1
LEFTJOIN[成就表]AS[T2]
ON[成就表].[先生ID]=[T2].[先生ID]AND[T2].[课程ID]=k2
LEFTJOIN[成就表]AS[T3]
ON[成就表].[先生ID]=[T3].[先生ID]AND[T3].[课程ID]=k3
LEFTJOIN[成就表]AS[T4]
ON[成就表].[先生ID]=[T4].[先生ID]AND[T4].[课程ID]=k4
ORDERBYISNULL([T1].[成就],0)+ISNULL([T2].[成就],0)+ISNULL([T3].[成就],0)+ISNULL([T4].[成就],0)DESC)

最初还要多说一句:一样平常TOP关头字与ORDERBY子句适用才有真正意义。

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

只管外表看上往不那末简单,实在用CASE能够很简单地完成:

SELECT课程ID,课程称号
,SUM(CASEWHEN成就BETWEEN85AND100THEN1ELSE0END)AS[100-85]
,SUM(CASEWHEN成就BETWEEN70AND85THEN1ELSE0END)AS[85-70]
,SUM(CASEWHEN成就BETWEEN60AND70THEN1ELSE0END)AS[70-60]
,SUM(CASEWHEN成就<60THEN1ELSE0END)AS[60-]
FROM成就表
GROUPBY课程ID,课程称号

注重这里的BETWEEN,固然字段名都是从高至低,可BETWEEN中仍是要从低到高,这里
假如不当心,会犯一个很难发明的逻辑毛病:在数学上,当a>b时,[a,b]是一个空集。

9.列印先生均匀成就及其名次

selectcount(distinctb.f)as名次,a.先生ID,max(a.先生姓名),max(a.f)
from(selectdistinctt.先生ID,t.先生姓名,(selectavg(成就)
fromtt1
wheret1.先生id=t.先生id)asF
fromT
)asa,
(selectdistinctt.先生ID,t.先生姓名,(selectavg(成就)
fromtt1
wheret1.先生id=t.先生id)asF
fromT
)asb
wherea.f<=b.f
groupbya.先生ID
orderbycount(b.f)

这里有良多值得一提的中央,先使用两个完整不异的自相干子查询天生两个派生表作
为基础表用于作小于或即是的毗连,如许就能够经由过程表中小于或即是每一个值的其他值
的COUNT(distinct)的计数会萃函数来表现名次了。

SELECT1+(SELECTCOUNT(distinct[均匀成就])
FROM(SELECT[先生ID],MAX([先生姓名])AS先生姓名,AVG([成就])AS[均匀成就]
FROMT
GROUPBY[先生ID]
)AST1
WHERE[均匀成就]>T2.[均匀成就])as名次,
[先生ID],[先生姓名],[均匀成就]
FROM(SELECT[先生ID],max([先生姓名])AS先生姓名,AVG([成就])AS[均匀成就]
FROMT
GROUPBY[先生ID]
)AST2
ORDERBYT2.[均匀成就]desc

办法二也利用了两个完整不异的自相干子查询天生两个派生表作为基础表,再使用它
们之间作年夜于的相干子查询取COUNT(distinct)+1的计数会萃函数一样完成了名
次的显现。

这道题从使用角度来看,查询了局是相称公道的,并列情形的名次也都一样。但假如想
完成相似主动序列的行号,该办理计划的范围性突显,不克不及处置并列相称的情形了,所
以有需要夸大:必定要选择不反复的毗连前提,能够依据实践情形使用字段组合的不等
毗连(T1.f1+...+T1.fn<=T2.f1+...+T2.fn)。持续引伸还能够经由过程判别
COUNT(distinct)%2是不是为0的HAVING或WHERE子句完成只显现偶数或奇数行:

HAVINGcount(distinctb.f)%2=1
或:
WHERE1+(SELECTCOUNT(distinct[均匀成就])
FROM(SELECT[先生ID],MAX([先生姓名])AS先生姓名,AVG([成就])AS[均匀成就]
FROMT
GROUPBY[先生ID]
)AST1
WHERE[均匀成就]>T2.[均匀成就])%2=1

再复杂说一下HAVING和WHERE在含有GROUPBY分组的查询中的区分,HAVING是
在数据分组后才选择纪录的,WHERE是先辈行选择在分组的,并且HAVING一样平常应与聚
集函数适用才有真正寄义。

两种办法再次表现了子查询与毗连能够异曲同工之妙,第二种子查询办法值得保举,因
为对照利于程序机关,便于为没有该功效的原有查询增加此项功效。本题仅仅是为了示
范一种对照新奇的解题思绪,躲避了效力的成绩。


10.列印各科成就前三名的纪录:(不思索成就并列情形)
先生ID,先生姓名,课程ID,课程称号,成就,教员ID,教员姓名

假如仅从成就思索前三名的人,使用相干子查询的常识:

SELECT*
FROM成就表t1
WHERE成就IN(SELECTTOP3成就
FROM成就表
WHEREt1.课程id=课程id
ORDERBY成就DESC
)
ORDERBYt1.课程id

如许查询的了局各科成就前三名的纪录数应当年夜于即是三,由于大概有并列情形,
假如小于三天然是该门课还没有那末多人测验!
假如不思索并列情形,严厉把持各科只列印三笔记录,则利用"先生id"机关相干
子查询前提亦可:

SELECT*
FROM成就表t1
WHERE先生idIN(SELECTTOP2先生id
FROM成就表
WHEREt1.课程id=课程id
ORDERBY成就DESC
)
ORDERBYt1.课程id

假如使用第10题的思绪也可完成该使用。

11.标准化
标准化的成绩能够说是仁者见仁,智者见智。并且不做一定欠好,但矫枉过正,弄到太
标准也纷歧定是功德。起首剖析信息的对应干系,这个表中有四种信息。先生、课程、教员、成就。个中前三个能够自力存在,最
后一个能够看作是基于前三个存在的。然后,我们按这四种分类,创建四个表:
关于先生的信息,有以下两个:先生ID,姓名;
教员则会有教员ID,姓名,课程ID这也就是为何我要把先生和教员会为两个表的缘故原由;
课程则有课程ID,课程称号两种;
而最初一个成就信息,就成了连接它们的一个部分,在这里,它要有先生ID,教员ID,课程ID,成就四项,相
对与别的表应属使用级别,除成就字段,别的都援用的别的的表。
如许一来,几个表的剧本也许是这个模样:
CREATETABLE"先生信息"
(
"ID"CHAR(4),
"姓名"CHAR(16),
PRIMARYKEY("ID")
)

CREATETABLE"课程信息"
(
"ID"CHAR(4),
"称号"CHAR(16),
PRIMARYKEY("ID"),
)

CREATETABLE"教员信息"
(
"ID"CHAR(4),
"姓名"CHAR(16),
"课程ID"CHAR(4),
PRIMARYKEY("ID"),
FOREIGNKEY("课程ID")REFERENCES"课程信息"("ID")
)

CREATETABLE"成就信息"
(
"先生ID"CHAR(4),
"教员ID"CHAR(4),
"课程ID"CHAR(4),
成就NUMERIC(5,2),
PRIMARYKEY("先生ID","教员ID","课程ID"),
FOREIGNKEY("先生ID")REFERENCES"先生信息"("ID"),
FOREIGNKEY("教员ID")REFERENCES"教员信息"("ID"),
FOREIGNKEY("课程ID")REFERENCES"课程信息"("ID")
)

如许建表很分明是为了尽量的细化信息的分类。它的优点在于各类信息分划明白,不
干涉题也很分明,好比,一个教员不克不及同时带两门分歧的课(固然,这大概恰是营业划定规矩所
请求的),并且,如许做分类过于精致了。

假如不必要对教员举行人事办理,那末,完整能够把教员信息和课程信息合为一表。也就是说,分歧教员带的同
一位称课程,视做分歧课程。如许做固然也有其使用背景,良多教员,出格是初等教导和名师,常常有他们自
己的作风,完整能够视做两种课程,信任一样传授C++,Lippman和Stroustrup教出的先生总会有所分歧。
要说成绩,那就是,假如想要限定先生不克不及反复修某一门课,就得用触发器了,没有太好的举措,不外这个成绩,
后面的第一种计划一样办理不了,就算针对教员和课程的干系单建一个表也纷歧定就能够,还把成绩庞大化了。
如今把第二种计划的剧本列出来:

CREATETABLE"先生信息"
(
"ID"CHAR(4),
"姓名"CHAR(16),
PRIMARYKEY("ID")
)

CREATETABLE"课程信息"
(
"ID"CHAR(4),
"课程分类"CHAR(4),
"称号"CHAR(16),
"教员ID"CHAR(4),
"教员姓名"CHAR(16),
PRIMARYKEY("ID")
)

CREATETABLE"成就信息"
(
"先生ID"CHAR(4),
"课程ID"CHAR(4),
成就NUMERIC(5,2),
PRIMARYKEY("先生ID","课程ID"),
FOREIGNKEY("先生ID")REFERENCES"先生信息"("ID"),
FOREIGNKEY("课程ID")REFERENCES"课程信息"("ID")-
)

如许是否是能清新一点?如许一来,假如不存在一个教员教分歧的课程的情形,而且我
们但愿简化办理,乃至都能够不必"课程分类"和"教员ID"字段。固然,视营业必要而定,
假如但愿在限定先生进修的课程分类的同时,不想带来分外的功能开支,利用第一种设
计,或将课程分类字段也列进成就信息表,是一个更好的举措。

关于数据库的计划和办理,有几条履历,拿出来在这里和人人交换一下:
对数据举行标准化时,最好要切合它的使用背景。如许易于了解和办理;
数据的标准化纷歧定是越细化越好,粒度得当地年夜一点,前面的编程一样平常会简单一点;
虽然说不是越细越好,不外如果不做标准化,却几近是必定要出成绩;
很主要的一点:万万不要滥用主动标识列!出格是,不要滥用主动标识列来做为一个表中独一的束缚前提,一般,
那和没有束缚没甚么分歧!

关于这些试题,我们的意见就到这里,但愿伴侣们能够拿出更多更好的定见,我们一同会商。

原题含谜底:
CREATETABLE[T](
[ID][int]IDENTITY(1,1)NOTNULL,
[先生ID][varchar](50)NULL,
[先生姓名][varchar](50)NULL,
[课程ID][varchar](50)NULL,
[课程称号][varchar](50)NULL,
[成就][real]NULL,
[教员ID][varchar](50)NULL,
[教员姓名][varchar](50)NULL,
CONSTRAINT[PK_T]PRIMARYKEYCLUSTERED
(
[ID]
)ON[PRIMARY]
)ON[PRIMARY]
GO

INSERTINTOT([先生ID],[先生姓名],[课程ID],[课程称号],[成就],[教员ID],[教员姓名])
valueS(S3,王五,K2,语文,81,T2,王先生)
INSERTINTOT([先生ID],[先生姓名],[课程ID],[课程称号],[成就],[教员ID],[教员姓名])
valueS(S3,王五,K4,政治,53,T4,赵先生)
INSERTINTOT([先生ID],[先生姓名],[课程ID],[课程称号],[成就],[教员ID],[教员姓名])
valueS(S4,赵六,K1,数学,99,T1,张先生)
INSERTINTOT([先生ID],[先生姓名],[课程ID],[课程称号],[成就],[教员ID],[教员姓名])
valueS(S4,赵六,K2,语文,33,T2,王先生)
INSERTINTOT([先生ID],[先生姓名],[课程ID],[课程称号],[成就],[教员ID],[教员姓名])
valueS(S4,赵六,K4,政治,59,T4,赵先生)
INSERTINTOT([先生ID],[先生姓名],[课程ID],[课程称号],[成就],[教员ID],[教员姓名])
valueS(s1,张三,K4,政治,79,T4,赵先生)
INSERTINTOT([先生ID],[先生姓名],[课程ID],[课程称号],[成就],[教员ID],[教员姓名])
valueS(s1,张三,K1,数学,98,T1,张先生)
INSERTINTOT([先生ID],[先生姓名],[课程ID],[课程称号],[成就],[教员ID],[教员姓名])
valueS(s1,张三,K3,英语,69,T3,李先生)
INSERTINTOT([先生ID],[先生姓名],[课程ID],[课程称号],[成就],但我们知道，若使用statement，并没有上述需要的数据。试想binlog中记录了一句updatetsetf1=3whereid=3。怎么恢复呢？

一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

一个是把SQL语句写到客户端,可以使用DataSet进行加工;

我们学到了什么？思考问题的时候从表的角度来思考问

数据库物理框架没有变动undo和redo都放在数据库得transaction中，个人感觉是个败笔。如果说我们在设计数据库的时候考虑分多个数据库，可能能在一定程度上避免I/O效率问题。

对一张百万级别的表建游标，同时又没有什么过滤条件，取得游标效率是如果直接SQL查询百万条数据；如果再对每条记录做处理，耗时将更长。

SP4包括用于以下SQLServer2000组件的程序包：Database组件（下载文件：SQL2000-KB884525-SP4-x86.EXE）更新SQLServer2000的32位Database组件，包括数据库引擎、复制、客户端连接组件及工具。有关其他信息，请参阅ReadmeSql2k32Sp4.htm。AnalysisServices组件（下载文件：SQL2000.AS-KB884525-SP4-x86.EXE）更新SQLServer2000的32位AnalysisServices。

只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。

SP4包括用于以下SQLServer2000组件的程序包：Database组件（下载文件：SQL2000-KB884525-SP4-x86.EXE）更新SQLServer2000的32位Database组件，包括数据库引擎、复制、客户端连接组件及工具。有关其他信息，请参阅ReadmeSql2k32Sp4.htm。AnalysisServices组件（下载文件：SQL2000.AS-KB884525-SP4-x86.EXE）更新SQLServer2000的32位AnalysisServices。