.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]    s cript Date: 05/11/2015 23:16:23 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Score]([stuID] [int] NOT NULL,[category] [varchar](50) NOT NULL,[score] [int] NOT NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [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]    s cript Date: 05/11/2015 23:16:23 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Department]([depID] [int] IDENTITY(1,1) NOT NULL,[depName] [varchar](50) NOT NULL,PRIMARY KEY CLUSTERED([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]GOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[Department] ONINSERT [dbo].[Department] ([depID], [depName]) VALUES (1, N 计较机 )INSERT [dbo].[Department] ([depID], [depName]) VALUES (2, N 生物 )INSERT [dbo].[Department] ([depID], [depName]) VALUES (3, N 数学 )SET IDENTITY_INSERT [dbo].[Department] OFF/****** Object:  Table [dbo].[Student]    s cript Date: 05/11/2015 23:16:23 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Student]([stuID] [int] IDENTITY(1,1) NOT NULL,[stuName] [varchar](50) NOT NULL,[deptID] [int] NOT NULL,PRIMARY KEY CLUSTERED([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]GOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[Student] ONINSERT [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)SET IDENTITY_INSERT [dbo].[Student] OFF/****** Object:  Default [DF__Departmen__depNa__5441852A]    s cript Date: 05/11/2015 23:16:23 ******/ALTER TABLE [dbo].[Department] ADD  DEFAULT ( ) FOR [depName]GO/****** Object:  Default [DF__Score__category__5EBF139D]    s cript Date: 05/11/2015 23:16:23 ******/ALTER TABLE [dbo].[Score] ADD  DEFAULT ( ) FOR [category]GO/****** Object:  Default [DF__Score__score__5FB337D6]    s cript Date: 05/11/2015 23:16:23 ******/ALTER TABLE [dbo].[Score] ADD  DEFAULT ((0)) FOR [score]GO/****** Object:  Default [DF__Student__stuName__59063A47]    s cript Date: 05/11/2015 23:16:23 ******/ALTER TABLE [dbo].[Student] ADD  DEFAULT ( ) FOR [stuName]GO/****** Object:  ForeignKey [FK__Student__deptID__59FA5E80]    s cript Date: 05/11/2015 23:16:23 ******/ALTER TABLE [dbo].[Student]  WITH CHECK ADD FOREIGN KEY([deptID])REFERENCES [dbo].[Department] ([depID])GO 筹办状况 3 成果 口试的时辰，没有写出来，那时脑壳昏沉沉的。也确切很久没有写复杂的sql语句了。明天花了2到3个小时，究竟试出来了。不明白有无更好的写法？-- 每一个系里的最高分的先生信息SELECT Department.depID, Department.depName, Student.stuID, stuName, Dscore.scoresFROM DepartmentLEFT JOIN Studenton department.depID = student.deptIDLEFT JOIN (SELECT Score.stuId, SUM(Score) AS scoresFROM ScoreGROUP by stuID) AS Dscoreon Student.stuID = dScore.stuIDwhere exists    (select *from(SELECT deptID, MAX(scores) AS topScoresFROM StudentLEFT JOIN(SELECT stuID,SUM(score) AS scoresFROM ScoreGROUP BY stuID) AS newScoreON Student.stuID = newScore.stuIDgroup by deptID) AS depScorewhere Department.depID = depScore.deptID and Dscore.scores=depScore.topScores)order by Department.depID,Student.stuID;4 补偿 看了那末多的评论，自身写的真的不咋样，可惜明天没有时分细细看了，此刻还在公司加班！但百度一下的时分仍是有滴，So清算一下相干资料先。 （1）、SQL2005四个排名函数（row_number、rank、dense_rank和ntile）的比力 （2）、关于with as：应用WITH AS进步机能简化嵌套SQL 5 参考SQL 精确的谜底的成果是一样的，缺点的各有各的分歧，精确的谜底后的机能也各有各的分歧，不外呢，暂时没有水平往阐发它，可是有空会把这些全数看一遍.感激列位啦！【2015-05-13 23:44】 1、pursuer.chen SELECT B.depID,B.depName,B.stuID ,B.stuName,SUM(A.score )AS SUM_SCORE FROM Score A INNER JOIN (SELECT SA.depID,SA.depName,S.stuID,S.stuName FROM Student S INNER JOIN Score SE ON S.stuID=SE.stuID INNER JOIN ( SELECT D.depID,D.depName ,MAX(SC.score )AS MX_score FROM Student S INNER JOIN Score SC ON S.stuID=SC.stuID INNER JOIN Department D ON S.deptID=D.depID GROUP BY D.depID,D.depName ) SA ON SE.score=SA.MX_score AND S.deptID=SA.depID ) B ON A.stuID=B.stuID GROUP BY B.depID,B.depName,B.stuID ,B.stuName ORDER BY B.depID,B.stuID 成果精确 计较机    2    计较机李四    169 生物    4    生物amy    152 生物    5    生物kity    178 数学    8    数学_haoxue    178 2、Gamain 精确 WITH cte1 as ( SELECT DISTINCT D.depID, D.depName, S.stuID, S.stuName, SUM(Sc.score) OVER (PARTITION BY D.depID,S.stuID) as sumScore FROM Department D LEFT JOIN Student S ON D.depID=S.deptID LEFT JOIN Score Sc ON Sc.stuID=S.stuID ), cte2 as ( SELECT DISTINCT depID, stuID, MAX(sumScore) OVER (PARTITION BY depID) as maxScore FROM cte1 ) SELECT c1.depID, c1.depName, c1.stuID, c1.stuName, c1.sumScore from cte2 c2 INNER JOIN cte1 c1 ON c1.depID=c2.depID AND c1.stuID=c2.stuID and c1.sumScore=c2.maxScore; 3、飞不动  精确 use test; select e.* from ( select c.depID,c.depName,a.stuID,b.stuName,a.total from (select stuID,sum(score) as total from Score group by stuID) a join Student b on b.stuID=a.stuID join Department c on c.depID=b.deptID ) e join (select b.deptID,max(a.total) maxScore from (select stuID,sum(score) as total from Score group by stuID) a join Student b on b.stuID=a.stuID group by b.deptID ) f on e.depID=f.deptID and e.total=f.MaxScore order by e.depID,e.stuID 4、之路  缺点 select depID, depName, stuId, stuName, PerTotalScore from ( select stuID, stuName, depID, depName, PerTotalScore, ROW_NUMBER() OVER(partition by depID order by PerTotalScore) as RowId from ( select distinct s.stuID, s.stuName, d.depID, d.depName, SUM(c.score) OVER(partition by d.depID,s.stuID) as PerTotalScore from dbo.student s JOIN dbo.Department d on s.deptID=d.depID JOIN dbo.Score c ON s.StuID=c.StuID ) as T ) as TT WHERE TT.RowId=1 order by depID,stuID 计较机    1    计较机张三    150 生物    4    生物amy    152 数学    9    数学_wuyong    141 5、King兵  精确 WITH a AS (SELECT Department.depID, Department.depName, Student.stuID, stuName, Dscore.scores,ROW_NUMBER() OVER(PARTITION BY Department.depID ORDER BY scores DESC) ROWID FROM Department LEFT JOIN Student on department.depID = student.deptID LEFT JOIN (SELECT Score.stuId, SUM(Score) AS scores FROM Score GROUP by stuID ) AS Dscore on Student.stuID = dScore.stuID), b AS ( SELECT Department.depID, Department.depName, Student.stuID, stuName, Dscore.scores,ROW_NUMBER() OVER(PARTITION BY Department.depID ORDER BY scores DESC) ROWID FROM Department LEFT JOIN Student on department.depID = student.deptID LEFT JOIN (SELECT Score.stuId, SUM(Score) AS scores FROM Score GROUP by stuID ) AS Dscore on Student.stuID = dScore.stuID ) SELECT depID, depName, stuID, stuName, scores,ROWID FROM a WHERE a.scores = (SELECT MAX(scores) FROM b c WHERE a.depid = c.depid) 6、 怪咖Eric  精确 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; SELECT D.* FROM ( SELECT de.depID, de.depName, st.stuID, st.stuName, sc.score, RANK() OVER( PARTITION BY st.deptID ORDER BY sc.score DESC ) rowno FROM Student st LEFT JOIN Department de ON de.depID=st.deptID LEFT JOIN ( SELECT sc.stuID, SUM(sc.score) score FROM Score sc GROUP BY sc.stuID ) sc ON sc.stuID=st.stuID ) D WHERE D.rowno = 1 --看错请求，原本只需列出最高分 ORDER BY D.depID, D.rowno 8、精确 Li.zheng use test; select * from ( select (select depName from Department where Department.depID = a.depID) as depName, (select stuName from Student where Student.stuID = a.stuID) as stuName, dense_rank() over(partition by depID order  by sumScore desc) as rank, a.sumScore from ( select c.depID,b.stuid,sum(a.score) as sumScore from score as a inner join Student as b on a.stuid = b.stuid inner join Department as c on c.depID = b.deptID group by c.depID,b.stuid ) as a ) as b where b.rank = 1 9、下个路口  缺点 漏了并列第一 SELECT * FROM   ( SELECT s1.stuID,s1.stuName,s1.deptID,t.totalScore,d.depName, ROW_NUMBER() OVER(PARTITION BY d.depID ORDER BY totalScore DESC) AS Rn FROM   Student AS s1 INNER JOIN ( SELECT s.stuID,SUM(s2.score) AS totalScore FROM Student AS s INNER JOIN Department AS d ON  d.depID = s.deptID INNER JOIN Score s2 ON s2.stuID = s.stuID GROUP BY s.stuID ) AS t ON  t.stuID = s1.stuID INNER JOIN Department AS d ON  d.depID = s1.deptID ) result WHERE Rn = 1 ORDER BY result.stuID 9、自由_   精确 select d.depID,d.depName,s.stuID,s.stuName,t.score from Department d left join (select s.stuID,sum(s.score) as score,st.deptID, rank() over(partition by st.deptID order by sum(s.score) desc) ra from Score s left join Student st on s.stuID = st.stuID group by s.stuID,st.deptID) t on d.depID = t.deptID left join Student s on t.stuID = s.stuID where t.ra = 1 order by d.depID,s. 10、 手写 改了 以后 缺点， use test; with Combin AS ( SELECT MAX(score) AS 最高分,deptID AS 系编号,MAX(a.stuID) AS 先生Id FROM Student a LEFT JOIN Score b ON a.stuID=b.stuID GROUP BY a.deptID ) SELECT c.系编号, (SELECT depName FROM Department d WHERE d.depID=c.系编号 ) AS 系名, c.先生Id AS 先生编号 , (SELECT stuName FROM Student e WHERE e.stuID=c.先生Id ) AS 姓名 , c.最高分 FROM Combin c 计较机    3    计较机王五    89 生物    6    生物lucky    91 数学    9    数学_wuyong    97 11、 舍长   精确 use test; WITH T1 AS ( SELECT A.DEPID,A.DEPNAME,B.STUID,B.STUNAME,SUM(C.SCORE) AS TotalScore FROM Department A INNER JOIN Student B ON A.DEPID = B.DEPTID INNER JOIN Score C ON B.STUID = C.STUID GROUP BY A.DEPID,A.DEPNAME,B.STUID,B.STUNAME ), T2 AS ( SELECT *,RANK() OVER(PARTITION BY DEPID ORDER BY TotalScore DESC) AS RankScore  FROM T1 ) SELECT * FROM T2 WHERE RankScore = 1 ORDER BY DEPID,STUID 12、Ender.Lu   精确 with tscore as (select stuID ,sum(score) as score from dbo.Score group by stuID), tinfo as (select Student.deptID ,Department.depName,dbo.Student.stuID,dbo.Student.stuName,tscore.score from dbo.Student inner join [dbo].[Department] on dbo.Department.depID = student.deptID left join tscore on tscore.stuid = Student.stuID), trank as ( select deptID ,depName,stuID,stuName,score ,rank() over(partition by  deptID  order by score desc) as level from tinfo ) select deptID ,depName,stuID,stuName,score from trank where level = 1 order by deptID ,stuID; 13、McJeremy Fan   精确 select p.totalscore,p.stuid,p.stuname,p.deptid,x.depname from ( select dense_rank() over(partition by deptid order by totalscore desc) as num, a.totalscore,b.stuid,b.stuname,b.deptid from ( select stuid,sum(score) as totalscore from score group by stuid ) a inner join student b on a.stuid=b.stuid ) as p inner join department x on p.deptid=x.depid where p.num=1 13、净水无大大鱼  精确 with temp as( select a.deptid,a.stuID,a.stuName,b.score from student a,(select stuID,sum(score)as score from score group by stuID)b where a.stuID=b.stuID) select d.depID,d.depName,b.stuID,b.stuName,b.score from Department d,( select * from temp t where t.score=( select max(score) from temp sc where t.deptid=sc.deptid)) b where d.depID=b.deptID order by depID,stuID 14、 BattleHeart  精确 SELECT D.*,DD.depName FROM ( SELECT C.stuID, C.TotleScore, C.stuName, C.deptID, DENSE_RANK() OVER(PARTITION BY C.deptID ORDER BY C.TotleScore DESC ) nubid FROM (SELECT S.stuID, ST.stuName, SUM(S.score) AS TotleScore, ST.deptID FROM dbo.Student AS ST INNER JOIN dbo.Score AS S ON S.stuID = ST.stuID GROUP BY S.stuID,ST.deptID,ST.stuName) AS C) AS D INNER JOIN dbo.Department AS DD ON DD.depID = D.deptID WHERE D.nubid=1