Sql Server 生成数据透视表（交叉分析数据）

最新推荐文章于 2026-06-15 19:52:53 发布

原创最新推荐文章于 2026-06-15 19:52:53 发布 · 6.6k 阅读

6 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sql server #sql #insert #null #2010

SQL SERVER 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何使用SQL创建学生信息、课程信息及成绩信息表，并插入基础数据。此外，还展示了如何通过动态生成SQL查询语句来创建数据透视表，以便进行更高效的数据分析。

[表一学生信息表]

[表二课程信息表]

[表三成绩信息表]

生成基础数据的代码

CREATE TABLE [dbo].[StuInfo] (
[StuID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[StuName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CourseInfo] (
[CourseID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CourseName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ScoreInfo] (
[StuID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CourseID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Score] [float] NULL
) ON [PRIMARY]
GO

insert stuInfo(StuID,StuName) values ('090301','张三')
insert stuInfo(StuID,StuName) values ('090302','李四')
insert stuInfo(StuID,StuName) values ('090303','王五')

insert CourseInfo(CourseID,CourseName) values ('201001','数学')
insert CourseInfo(CourseID,CourseName) values ('201002','C语言')

insert ScoreInfo(StuID,CourseID,Score) values ('090301','201001',90)
insert ScoreInfo(StuID,CourseID,Score) values ('090301','201002',100)
insert ScoreInfo(StuID,CourseID,Score) values ('090302','201001',95)
insert ScoreInfo(StuID,CourseID,Score) values ('090302','201002',98)
insert ScoreInfo(StuID,CourseID,Score) values ('090303','201001',88)

生成数据透视表

(1)单表 ScoreInfo
declare @sql nvarchar(500)
set @sql = 'SELECT [StuID], '
select @sql = @sql + 'sum(case CourseID when '+quotename(CourseID,'''')+' then score else 0 end) as ' + quotename(CourseID)+',' from ScoreInfo group by CourseID
select @sql = left(@sql,len(@sql)-1)
select @sql = @sql + ', sum(score) as [sum] from ScoreInfo group by [StuID]'
select @sql
exec(@sql)

执行效果图：

(2)双表 StuInfo,ScoreInfo
declare @sql nvarchar(500)
set @sql = 'SELECT (select stuName from StuInfo where StuInfo.stuID=scoreInfo.stuID), '
select @sql = @sql + 'sum(case CourseID when '+quotename(CourseID,'''')+' then score else 0 end) as ' + quotename(CourseID)+',' from ScoreInfo group by CourseID
select @sql = left(@sql,len(@sql)-1)
select @sql = @sql + ', sum(score) as [sum] from ScoreInfo group by [StuID]'
select @sql
exec(@sql)
执行效果图：

(3)多表 StuInfo,ScoreInfo,ScoreInfo
declare @sql nvarchar(500)
set @sql = 'SELECT (select stuName from StuInfo where StuInfo.stuID=scoreInfo.stuID) as stuName, '
select @sql = @sql + 'sum(case CourseID when '+quotename(CourseID,'''')+' then score else 0 end) as ' + quotename((select courseName from courseInfo where

courseInfo.CourseID=ScoreInfo.CourseID),'''')+',' from ScoreInfo group by CourseID
select @sql = left(@sql,len(@sql)-1)
select @sql = @sql + ', sum(score) as [sum] from ScoreInfo group by [StuID]'
select @sql
exec(@sql)

执行效果图：