- 相關推薦
.NET高級工程師面試題之SQL篇(2)
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;
http://www.ipr-jzsc.com/【.NET高級工程師面試題之SQL篇(2)】相關文章：
考研復試之英語面試題目07-24
德國公司經典面試題(2)07-10
經典臺詞之《武林外傳》(2)07-18
關于net論文的開題報告模板06-09
職業生涯規劃之興趣(2)07-21
Microsoft面試題09-04
iOS面試題07-10
公司面試題09-12
hibernate面試題10-18
英語面試題精選06-13