sql实现数据透视表_MySQL数据库下载

展开全部

举例说明e58685e5aeb6237怎么用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 (”,’数学’)

insert CourseInfo(CourseID,CourseName) values (”,’C语言’)

insert ScoreInfo(StuID,CourseID,Score) values (‘090301’,”,90)

insert ScoreInfo(StuID,CourseID,Score) values (‘090301’,”,100)

insert ScoreInfo(StuID,CourseID,Score) values (‘090302’,”,95)

insert ScoreInfo(StuID,CourseID,Score) values (‘090302’,”,98)

insert ScoreInfo(StuID,CourseID,Score) values (‘090303’,”,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)

执行效果图：