.NET高級工程師面試題之SQL篇

fff8 9年前發布 | 17K 次閱讀 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 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Score](
    [stuID] [int] NOT NULL,
    [category] [varchar](50) NOT NULL,
    [score] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
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 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_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'數學')
SET IDENTITY_INSERT [dbo].[Department] OFF
/****** Object:  Table [dbo].[Student]    Script Date: 05/11/2015 23:16:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_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)
SET IDENTITY_INSERT [dbo].[Student] OFF
/****** Object:  Default [DF__Departmen__depNa__5441852A]    Script Date: 05/11/2015 23:16:23 ******/
ALTER TABLE [dbo].[Department] ADD  DEFAULT ('') FOR [depName]
GO
/****** Object:  Default [DF__Score__category__5EBF139D]    Script Date: 05/11/2015 23:16:23 ******/
ALTER TABLE [dbo].[Score] ADD  DEFAULT ('') FOR [category]
GO
/****** Object:  Default [DF__Score__score__5FB337D6]    Script Date: 05/11/2015 23:16:23 ******/
ALTER TABLE [dbo].[Score] ADD  DEFAULT ((0)) FOR [score]
GO
/****** Object:  Default [DF__Student__stuName__59063A47]    Script Date: 05/11/2015 23:16:23 ******/
ALTER TABLE [dbo].[Student] ADD  DEFAULT ('') FOR [stuName]
GO
/****** Object:  ForeignKey [FK__Student__deptID__59FA5E80]    Script 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.scores
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
where exists    (    
select *
from
(        
 SELECT deptID, MAX(scores) AS topScores
 FROM Student
 LEFT JOIN 
        (
 SELECT stuID,SUM(score) AS scores
 FROM Score
 GROUP BY stuID) AS newScore
 ON Student.stuID = newScore.stuID
 group by deptID) AS depScore
 where 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

本文由用戶 fff8 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1432649167410.html

SQL

.NET高級工程師面試題之SQL篇

相關經驗

相關資訊

相關文檔

目錄