????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 ???
???????????????д????????????????????????t????д?????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