----查询学生各科成绩报表，一步一步分析
学生表
USE [Test] GO /****** Object: Table [dbo].[student] Script Date: 2017/7/10 12:17:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[student]( [stuId] [NVARCHAR](50) NOT NULL, [stuName] [VARCHAR](10) NOT NULL, [stuSex] [CHAR](2) NULL, [age] [INT] NULL, CONSTRAINT [PK__student__AEC9BF8FBC61E94F] 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 ALTER TABLE [dbo].[student] WITH CHECK ADD CONSTRAINT [CK__student__age__117F9D94] CHECK (([age]>=(15) AND [age]<=(45))) GO ALTER TABLE [dbo].[student] CHECK CONSTRAINT [CK__student__age__117F9D94] GO ALTER TABLE [dbo].[student] WITH CHECK ADD CONSTRAINT [CK__student__age__1920BF5C] CHECK (([age]>=(15) AND [age]<=(45))) GO ALTER TABLE [dbo].[student] CHECK CONSTRAINT [CK__student__age__1920BF5C] GO ALTER TABLE [dbo].[student] WITH CHECK ADD CONSTRAINT [CK__student__stuSex__108B795B] CHECK (([stuSex]='男' OR [stuSex]='女')) GO ALTER TABLE [dbo].[student] CHECK CONSTRAINT [CK__student__stuSex__108B795B] GO ALTER TABLE [dbo].[student] WITH CHECK ADD CONSTRAINT [CK__student__stuSex__1A14E395] CHECK (([stuSex]='男' OR [stuSex]='女')) GO ALTER TABLE [dbo].[student] CHECK CONSTRAINT [CK__student__stuSex__1A14E395] GO 科目表 USE [Test] GO /****** Object: Table [dbo].[course] Script Date: 2017/7/10 12:17:54 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[course]( [couId] [NVARCHAR](50) NOT NULL, [couName] [VARCHAR](20) NOT NULL, CONSTRAINT [PK__course__965389F7BE4EE6DC] PRIMARY KEY CLUSTERED ( [couId] 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成绩表
USE [Test] GO /****** Object: Table [dbo].[score] Script Date: 2017/7/10 12:19:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[score]( [stuId] [NVARCHAR](50) NOT NULL, [couId] [NVARCHAR](50) NOT NULL, [grade] [INT] NULL, CONSTRAINT [PK__score__C7AC871022E01D82] PRIMARY KEY CLUSTERED ( [stuId] ASC, [couId] 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 ALTER TABLE [dbo].[score] WITH CHECK ADD CONSTRAINT [fk_couId] FOREIGN KEY([couId]) REFERENCES [dbo].[course] ([couId]) GO ALTER TABLE [dbo].[score] CHECK CONSTRAINT [fk_couId] GO ALTER TABLE [dbo].[score] WITH CHECK ADD CONSTRAINT [fk_stuId] FOREIGN KEY([stuId]) REFERENCES [dbo].[student] ([stuId]) GO ALTER TABLE [dbo].[score] CHECK CONSTRAINT [fk_stuId] GO ALTER TABLE [dbo].[score] WITH CHECK ADD CONSTRAINT [CK__score__grade__164452B1] CHECK (([grade]>=(0) AND [grade]<=(100))) GO ALTER TABLE [dbo].[score] CHECK CONSTRAINT [CK__score__grade__164452B1] GO ----1.查询所有学生信息以及各科成绩集合 SELECT s.*,c.grade,r.couName FROM dbo.student AS s LEFT JOIN dbo.score AS c ON c.stuId=s.stuId LEFT JOIN dbo.course AS r ON c.couId=r.couId ----2.将各科成绩转换成一行 SELECT s.stuId,s.stuName,s.stuSex,s.age, CASE r.couName WHEN '数学' THEN c.grade ELSE 0 END AS '数学', CASE r.couName WHEN '语文' THEN c.grade ELSE 0 END AS '语文', CASE r.couName WHEN '英语' THEN c.grade ELSE 0 END AS '英语', CASE r.couName WHEN '历史' THEN c.grade ELSE 0 END AS '历史' FROM dbo.student AS s LEFT JOIN dbo.score AS c ON c.stuId=s.stuId LEFT JOIN dbo.course AS r ON c.couId=r.couId ----3.统计成绩总数，并分组 SELECT s.stuId,s.stuName, SUM(CASE r.couName WHEN '数学' THEN c.grade ELSE 0 END) AS '数学', SUM(CASE r.couName WHEN '语文' THEN c.grade ELSE 0 END) AS '语文', SUM(CASE r.couName WHEN '英语' THEN c.grade ELSE 0 END) AS '英语', SUM(CASE r.couName WHEN '历史' THEN c.grade ELSE 0 END) AS '历史' FROM dbo.student AS s LEFT JOIN dbo.score AS c ON c.stuId=s.stuId LEFT JOIN dbo.course AS r ON c.couId=r.couId GROUP BY s.stuId,s.stuName ----4.或max（）者也可以 SELECT s.stuId,s.stuName, max(CASE r.couName WHEN '数学' THEN c.grade ELSE 0 END) AS '数学', max(CASE r.couName WHEN '语文' THEN c.grade ELSE 0 END) AS '语文', max(CASE r.couName WHEN '英语' THEN c.grade ELSE 0 END) AS '英语', max(CASE r.couName WHEN '历史' THEN c.grade ELSE 0 END) AS '历史' FROM dbo.student AS s LEFT JOIN dbo.score AS c ON c.stuId=s.stuId LEFT JOIN dbo.course AS r ON c.couId=r.couId GROUP BY s.stuId,s.stuName