----查询学生各科成绩报表,一步一步分析
学生表
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