1 題目

這確實(shí)是一個(gè)真實(shí)的面試題，琢磨一下吧！知識(shí)不用，就會(huì)丟掉，我太依賴各種框架和dll了，已經(jīng)忘記了最基本的東西。有多久沒有寫過SQL了，我已經(jīng)不記得了。

[[133713]]

已知表信息如下：

Department(depID, depName),depID 系編號(hào)，DepName系名

Student(stuID, name, depID) 學(xué)生編號(hào)，姓名，系編號(hào)

Score(stuID, category, score) 學(xué)生編碼，科目，成績(jī)

找出每一個(gè)系的最高分，并且按系編號(hào)，學(xué)生編號(hào)升序排列，要求順序輸出以下信息：

系編號(hào)，系名，學(xué)生編號(hào)，姓名，總分

2 實(shí)驗(yàn)

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'英語(yǔ)', 80) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (2, N'數(shù)學(xué)', 80) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (1, N'數(shù)學(xué)', 70) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (2, N'英語(yǔ)', 89) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (3, N'英語(yǔ)', 81) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (3, N'數(shù)學(xué)', 71) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (4, N'數(shù)學(xué)', 91) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (4, N'英語(yǔ)', 61) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (5, N'英語(yǔ)', 91) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (6, N'英語(yǔ)', 89) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (7, N'英語(yǔ)', 77) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (8, N'英語(yǔ)', 97) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (9, N'英語(yǔ)', 57) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (5, N'數(shù)學(xué)', 87) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (6, N'數(shù)學(xué)', 89) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (7, N'數(shù)學(xué)', 80) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (8, N'數(shù)學(xué)', 81) 
INSERT [dbo].[Score] ([stuID], [category], [score]) VALUES (9, N'數(shù)學(xué)', 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'計(jì)算機(jī)') 
INSERT [dbo].[Department] ([depID], [depName]) VALUES (2, N'生物') 
INSERT [dbo].[Department] ([depID], [depName]) VALUES (3, N'數(shù)學(xué)') 
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'計(jì)算機(jī)張三', 1) 
INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (2, N'計(jì)算機(jī)李四', 1) 
INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (3, N'計(jì)算機(jī)王五', 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'數(shù)學(xué)_yiming', 3) 
INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (8, N'數(shù)學(xué)_haoxue', 3) 
INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (9, N'數(shù)學(xué)_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 
 
準(zhǔn)備環(huán)境 

3 結(jié)果

面試的時(shí)候，沒有寫出來，當(dāng)時(shí)腦袋昏沉沉的。也確實(shí)好久沒有寫復(fù)雜的sql語(yǔ)句了。今天花了2到3個(gè)小時(shí)，終于試出來了。不知道有沒有更好的寫法？

-- 每個(gè)系里的最高分的學(xué)生信息 
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;