.NET高级工程师面试题之SQL篇

1 题目

这确实是一个真实的面试题，琢磨一下吧！知识不用，就会丢掉，我太依赖各种框架和dll了，已经忘记了最基本的东西。有多久没有写过SQL了，我已经不记得了。

已知表信息如下：

Department(depID, depName),depID 系编号，DepName系名

Student(stuID, name, depID) 学生编号，姓名，系编号

Score(stuID, category, score) 学生编码，科目，成绩

找出每一个系的最高分，并且按系编号，学生编号升序排列，要求顺序输出以下信息：

系编号，系名，学生编号，姓名，总分

2 实验

USE [test]

GO

/****** Object:Table[dbo].[Score] Script Date: 05/11/2015 23:16:23 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_PADDINGON

GO

CREATETABLE[dbo].[Score](

[stuID] [int]NOTNULL,

[category] [varchar](50)NOTNULL,

[score] [int]NOTNULL

)ON[PRIMARY]

GO

SETANSI_PADDINGOFF

GO

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (1, N'英语', 80)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (2, N'数学', 80)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (1, N'数学', 70)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (2, N'英语', 89)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (3, N'英语', 81)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (3, N'数学', 71)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (4, N'数学', 91)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (4, N'英语', 61)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (5, N'英语', 91)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (6, N'英语', 89)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (7, N'英语', 77)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (8, N'英语', 97)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (9, N'英语', 57)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (5, N'数学', 87)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (6, N'数学', 89)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (7, N'数学', 80)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (8, N'数学', 81)

INSERT[dbo].[Score] ([stuID], [category], [score])VALUES (9, N'数学', 84)

/****** Object:Table[dbo].[Department] Script Date: 05/11/2015 23:16:23 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_PADDINGON

GO

CREATETABLE[dbo].[Department](

[depID] [int] IDENTITY(1,1)NOTNULL,

[depName] [varchar](50)NOTNULL,

PRIMARYKEYCLUSTERED

(

[depID]ASC

)WITH(PAD_INDEX =OFF, STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON)ON[PRIMARY]

)ON[PRIMARY]

GO

SETANSI_PADDINGOFF

GO

SETIDENTITY_INSERT [dbo].[Department]ON

INSERT[dbo].[Department] ([depID], [depName])VALUES(1, N'计算机')

INSERT[dbo].[Department] ([depID], [depName])VALUES(2, N'生物')

INSERT[dbo].[Department] ([depID], [depName])VALUES(3, N'数学')

SETIDENTITY_INSERT [dbo].[Department]OFF

/****** Object:Table[dbo].[Student] Script Date: 05/11/2015 23:16:23 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_PADDINGON

GO

CREATETABLE[dbo].[Student](

[stuID] [int] IDENTITY(1,1)NOTNULL,

[stuName] [varchar](50)NOTNULL,

[deptID] [int]NOTNULL,

PRIMARYKEYCLUSTERED

(

[stuID]ASC

)WITH(PAD_INDEX =OFF, STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON)ON[PRIMARY]

)ON[PRIMARY]

GO

SETANSI_PADDINGOFF

GO

SETIDENTITY_INSERT [dbo].[Student]ON

INSERT[dbo].[Student] ([stuID], [stuName], [deptID])VALUES (1, N'计算机张三', 1)

INSERT[dbo].[Student] ([stuID], [stuName], [deptID])VALUES (2, N'计算机李四', 1)

INSERT[dbo].[Student] ([stuID], [stuName], [deptID])VALUES (3, N'计算机王五', 1)

INSERT[dbo].[Student] ([stuID], [stuName], [deptID])VALUES (4, N'生物amy', 2)

INSERT[dbo].[Student] ([stuID], [stuName], [deptID])VALUES (5, N'生物kity', 2)

INSERT[dbo].[Student] ([stuID], [stuName], [deptID])VALUES (6, N'生物lucky', 2)

INSERT[dbo].[Student] ([stuID], [stuName], [deptID])VALUES (7, N'数学_yiming', 3)

INSERT[dbo].[Student] ([stuID], [stuName], [deptID])VALUES (8, N'数学_haoxue', 3)

INSERT[dbo].[Student] ([stuID], [stuName], [deptID])VALUES (9, N'数学_wuyong', 3)

SETIDENTITY_INSERT [dbo].[Student]OFF

/****** Object:Default[DF__Departmen__depNa__5441852A] Script Date: 05/11/2015 23:16:23 ******/

ALTERTABLE[dbo].[Department] ADDDEFAULT ('')FOR[depName]

GO

/****** Object:Default[DF__Score__category__5EBF139D] Script Date: 05/11/2015 23:16:23 ******/

ALTERTABLE[dbo].[Score] ADDDEFAULT ('')FOR[category]

GO

/****** Object:Default[DF__Score__score__5FB337D6] Script Date: 05/11/2015 23:16:23 ******/

ALTERTABLE[dbo].[Score] ADDDEFAULT ((0)) FOR[score]

GO

/****** Object:Default[DF__Student__stuName__59063A47] Script Date: 05/11/2015 23:16:23 ******/

ALTERTABLE[dbo].[Student] ADDDEFAULT ('')FOR[stuName]

GO

/****** Object: ForeignKey [FK__Student__deptID__59FA5E80] ScriptDate: 05/11/2015 23:16:23 ******/

ALTERTABLE[dbo].[Student] WITHCHECK ADD FOREIGN KEY([deptID])

REFERENCES[dbo].[Department] ([depID])

GO

3 结果

　　

面试的时候，没有写出来，当时脑袋昏沉沉的。也确实好久没有写复杂的sql语句了。今天花了2到3个小时，终于试出来了。不知道有没有更好的写法？

-- 每个系里的最高分的学生信息

SELECTDepartment.depID, Department.depName, Student.stuID, stuName, Dscore.scores

FROMDepartment

LEFTJOINStudent

ondepartment.depID = student.deptID

LEFTJOIN(SELECTScore.stuId,SUM(Score)ASscores

FROMScore

GROUPbystuID

)ASDscore

onStudent.stuID = dScore.stuID

whereexists (

select*

from

(

SELECTdeptID,MAX(scores)AStopScores

FROMStudent

LEFTJOIN

(

SELECTstuID,SUM(score)ASscores

FROMScore

GROUPBYstuID)ASnewScore

ONStudent.stuID = newScore.stuID

groupbydeptID)ASdepScore

whereDepartment.depID = depScore.deptIDandDscore.scores=depScore.topScores

)

orderbyDepartment.depID,Student.stuID;

4 补充

看了那么多的评论，自己写的真的不咋样，可惜今天没有时间细细看了，现在还在公司加班！但百度一下的时间还是有滴，So整理一下相关资料先。

（1）、SQL2005四个排名函数（row_number、rank、dense_rank和ntile）的比较

http://www.cnblogs.com/xhyang110/archive/2009/10/27/1590448.html

（2）、关于with as：使用WITH AS提高性能简化嵌套SQL

http://www.cnblogs.com/fygh/archive/2011/08/31/2160266.html

5 参考SQL

　　

正确的答案的结果是一样的，错误的各有各的不同，正确的答案后的性能也各有各的不同，不过呢，暂时没有水平去分析它，但是有空会把这些全部看一遍.谢谢各位啦！【2015-05-13 23:44】




1、pursuer.chen




SELECTB.depID,B.depName,B.stuID ,B.stuName,SUM(A.score )ASSUM_SCOREFROMScore A

INNERJOIN

(SELECTSA.depID,SA.depName,S.stuID,S.stuNameFROMStudent S

INNERJOINScore SEONS.stuID=SE.stuID

INNERJOIN(

SELECTD.depID,D.depName ,MAX(SC.score )ASMX_scoreFROMStudent SINNERJOINScore SCONS.stuID=SC.stuID INNERJOIN Department DONS.deptID=D.depID

GROUPBYD.depID,D.depName ) SAONSE.score=SA.MX_score ANDS.deptID=SA.depID )

BONA.stuID=B.stuID

GROUPBYB.depID,B.depName,B.stuID ,B.stuName

ORDERBYB.depID,B.stuID

结果正确




计算机 2 计算机李四 169

生物 4 生物amy 152

生物 5 生物kity 178

数学 8 数学_haoxue 178




2、Gamain 正确




WITHcte1as

(

SELECT

DISTINCT

D.depID,

D.depName,

S.stuID,

S.stuName,

SUM(Sc.score) OVER (PARTITIONBYD.depID,S.stuID) assumScore

FROMDepartment DLEFTJOINStudent SOND.depID=S.deptID

LEFTJOINScore ScONSc.stuID=S.stuID

), cte2as

(

SELECT

DISTINCT

depID,

stuID,

MAX(sumScore) OVER (PARTITIONBYdepID) asmaxScore

FROM

cte1

)

SELECT

c1.depID,

c1.depName,

c1.stuID,

c1.stuName,

c1.sumScore

fromcte2 c2INNERJOINcte1 c1

ONc1.depID=c2.depIDANDc1.stuID=c2.stuIDandc1.sumScore=c2.maxScore;

3、飞不动 正确

使用max函数的方式


use test;

select

e.*

from

(

selectc.depID,c.depName,a.stuID,b.stuName,a.totalfrom

(selectstuID,sum(score)astotal fromScore groupby stuID) a

joinStudent bonb.stuID=a.stuID

joinDepartment conc.depID=b.deptID

) e

join

(selectb.deptID,max(a.total) maxScorefrom

(selectstuID,sum(score)astotal fromScore groupby stuID) a

joinStudent bonb.stuID=a.stuID

groupbyb.deptID

) fone.depID=f.deptID ande.total=f.MaxScore

orderbye.depID,e.stuID




4、之路 错误




select

depID,

depName,

stuId,

stuName,

PerTotalScore

from(

select

stuID,

stuName,

depID,

depName,

PerTotalScore,

ROW_NUMBER() OVER(partitionbydepID orderby PerTotalScore)asRowId

from(

select

distinct

s.stuID,

s.stuName,

d.depID,

d.depName,

SUM(c.score) OVER(partitionbyd.depID,s.stuID) asPerTotalScore

fromdbo.student s

JOINdbo.Department dons.deptID=d.depID

JOINdbo.Score cONs.StuID=c.StuID )asT )asTT

WHERETT.RowId=1

orderbydepID,stuID

计算机 1 计算机张三 150

生物 4 生物amy 152

数学 9 数学_wuyong 141




5、King兵 正确




WITHa

AS

(SELECTDepartment.depID, Department.depName, Student.stuID, stuName, Dscore.scores,ROW_NUMBER() OVER(PARTITIONBY Department.depID ORDER BY scores DESC) ROWID

FROMDepartment

LEFTJOINStudent

ondepartment.depID = student.deptID

LEFTJOIN(SELECTScore.stuId,SUM(Score)ASscores

FROMScore

GROUPbystuID

)ASDscore

onStudent.stuID = dScore.stuID),

b

AS

(

SELECTDepartment.depID, Department.depName, Student.stuID, stuName, Dscore.scores,ROW_NUMBER() OVER(PARTITIONBYDepartment.depID ORDERBY scoresDESC) ROWID

FROMDepartment

LEFTJOINStudent

ondepartment.depID = student.deptID

LEFTJOIN(SELECTScore.stuId,SUM(Score)ASscores

FROMScore

GROUPbystuID

)ASDscore

onStudent.stuID = dScore.stuID

)

SELECTdepID, depName, stuID, stuName, scores,ROWIDFROM aWHERE a.scores = (SELECT MAX(scores) FROMb cWHEREa.depid = c.depid)




6、 怪咖Eric 正确

巧用PARTITION与RANK   推荐！！！！


    SELECT bb.deptID ,    cc.depName ,    bb.stuID ,    bb.stuName ,    bb.TotalScore    FROM ( SELECT * ,    RANK() OVER ( PARTITION BY deptID ORDER BY TotalScore DESC ) AS pos    FROM ( SELECT SUM(b.score) AS TotalScore ,    a.stuID ,    a.stuName ,    a.deptID    FROM Student a    JOIN Score b ON a.StuID = b.StuID    GROUP BY a.stuID ,    a.stuName ,    a.deptID    ) aa    ) bb    JOIN dbo.Department cc ON bb.deptID = cc.depID    JOIN dbo.Student dd ON bb.stuID = dd.stuID    WHERE pos = '1'    ORDER BY bb.deptID ,    bb.stuID




7、Michael Jiang 手写 改后正确

use test;

SELECTD.*

FROM(

SELECTde.depID,

de.depName,

st.stuID,

st.stuName,

sc.score,

RANK() OVER(

PARTITIONBYst.deptID

ORDERBYsc.scoreDESC

) rowno

FROMStudent st

LEFTJOINDepartment de

ONde.depID=st.deptID

LEFTJOIN(

SELECTsc.stuID,

SUM(sc.score) score

FROMScore sc

GROUPBYsc.stuID

) sc

ONsc.stuID=st.stuID

) D

WHERED.rowno = 1--看错要求，原来只要列出最高分

ORDERBYD.depID, D.rowno

8、正确 Li.zheng




use test;

select*from(

select

(selectdepNamefromDepartmentwhereDepartment.depID = a.depID)asdepName,

(selectstuNamefromStudentwhereStudent.stuID = a.stuID)asstuName,

dense_rank() over(partitionbydepID orderby sumScoredesc)asrank,

a.sumScore

from

(

select

c.depID,b.stuid,sum(a.score)assumScore

from

scoreasa

innerjoinStudentasb ona.stuid = b.stuid

innerjoinDepartmentasc onc.depID = b.deptID

groupby

c.depID,b.stuid

)asa

)asb whereb.rank = 1




9、下个路口 错误 漏了并列第一




SELECT*

FROM(

SELECTs1.stuID,s1.stuName,s1.deptID,t.totalScore,d.depName,

ROW_NUMBER() OVER(PARTITIONBYd.depID ORDERBY totalScoreDESC)AS

Rn

FROMStudentASs1

INNERJOIN(

SELECTs.stuID,SUM(s2.score)AStotalScore FROMStudent ASs

INNERJOINDepartmentASd ONd.depID = s.deptID

INNERJOINScore s2ONs2.stuID = s.stuID

GROUPBYs.stuID

)ASt

ONt.stuID = s1.stuID

INNERJOINDepartmentASd

ONd.depID = s1.deptID

) result

WHERERn = 1

ORDERBYresult.stuID




10、自由_ 正确




selectd.depID,d.depName,s.stuID,s.stuName,t.scorefrom Department dleft join

(selects.stuID,sum(s.score)asscore,st.deptID,

rank() over(partitionbyst.deptID orderby sum(s.score)desc) rafromScore s

leftjoinStudent stons.stuID = st.stuID groupby s.stuID,st.deptID) t

ond.depID = t.deptIDleftjoinStudent sont.stuID = s.stuID

wheret.ra = 1orderbyd.depID,s.




11、 手写 改了之后 错误




use test;

withCombinAS

(

SELECTMAX(score)AS最高分,deptID AS系编号,MAX(a.stuID)AS学生Id FROMStudent a LEFTJOIN Score bONa.stuID=b.stuID

GROUPBYa.deptID

)

SELECT

c.系编号,

(SELECTdepNameFROMDepartment dWHEREd.depID=c.系编号 )AS系名,

c.学生IdAS'学生编号',

(SELECTstuNameFROMStudent eWHEREe.stuID=c.学生Id )AS'姓名',

c.最高分

FROMCombin c

计算机 3 计算机王五 89

生物 6 生物lucky 91

数学 9 数学_wuyong 97




12、 舍长 正确




use test;

WITHT1AS(

SELECTA.DEPID,A.DEPNAME,B.STUID,B.STUNAME,SUM(C.SCORE)ASTotalScore

FROMDepartment A

INNERJOINStudent B

ONA.DEPID = B.DEPTID

INNERJOINScore C

ONB.STUID = C.STUID

GROUPBYA.DEPID,A.DEPNAME,B.STUID,B.STUNAME

),

T2AS(

SELECT*,RANK() OVER(PARTITIONBYDEPIDORDERBYTotalScoreDESC)ASRankScore FROMT1

)

SELECT*FROMT2WHERERankScore = 1ORDERBYDEPID,STUID




13、Ender.Lu 正确




with

tscoreas(selectstuID ,sum(score)asscore fromdbo.Score groupby stuID),

tinfoas(selectStudent.deptID ,Department.depName,dbo.Student.stuID,dbo.Student.stuName,tscore.scorefromdbo.Student

innerjoin[dbo].[Department]ondbo.Department.depID = student.deptID

leftjointscoreontscore.stuid = Student.stuID),

trankas(

selectdeptID ,depName,stuID,stuName,score ,rank() over(partitionbydeptID orderby scoredesc)aslevel from tinfo

)

selectdeptID ,depName,stuID,stuName,scorefromtrankwherelevel= 1 orderby deptID ,stuID;




14、McJeremy&Fan 正确




selectp.totalscore,p.stuid,p.stuname,p.deptid,x.depnamefrom

(

select

dense_rank() over(partitionbydeptid orderby totalscoredesc)asnum,

a.totalscore,b.stuid,b.stuname,b.deptid

from

(

selectstuid,sum(score)astotalscore fromscore

groupbystuid

) ainnerjoin student bona.stuid=b.stuid

)asp

innerjoindepartment xonp.deptid=x.depid

wherep.num=1




15、清水无大大鱼 正确




withtempas(

selecta.deptid,a.stuID,a.stuName,b.scorefromstudent a,(selectstuID,sum(score)asscorefromscoregroupbystuID)bwherea.stuID=b.stuID)

selectd.depID,d.depName,b.stuID,b.stuName,b.scorefrom Department d,(

select*fromtemptwheret.score=( selectmax(score)fromtempscwheret.deptid=sc.deptid)) b whered.depID=b.deptID orderby depID,stuID




16、 BattleHeart 正确




SELECTD.*,DD.depNameFROM(

SELECTC.stuID,

C.TotleScore,

C.stuName,

C.deptID,

DENSE_RANK() OVER(PARTITIONBYC.deptID ORDERBY C.TotleScoreDESC) nubid

FROM(SELECTS.stuID,

ST.stuName,

SUM(S.score)ASTotleScore,

ST.deptID

FROMdbo.StudentASST

INNERJOINdbo.ScoreASS ONS.stuID = ST.stuID

GROUPBYS.stuID,ST.deptID,ST.stuName)ASC) ASD INNERJOIN dbo.DepartmentASDD

ONDD.depID = D.deptIDWHERED.nubid=1