金沙澳门官网下载app-金沙澳门官网网址

行转列问题总结,4.    计算每个人的平均成绩

一、要求 1 创建数据表 CREATE TABLE [dbo].[StuScore]( [stuid] [int] NOT NULL, [subject] [nvarchar](30) NULL, [score] [decimal](5, 1) NULL ) 2 插入测试数据 stuid subject score 3 chinese 76.0 3 math 73.0 4 chinese 82.0 5 chinese 66.0 5 math 93.0 6 chinese 67.0 7 math 83.0 8 chinese 77.0 8 math 84.0 3 行转列后的结果 stuid chinese math 3 76.0 73.0 4 82.0 0.0 5 66.0 93.0 6 67.0 0.0 7 0.0 83.0 8 77.0 84.0 二 、分析 1 行转列,一个重点就是怎么样知道有多少列,怎么样创建这些列?我们可以先把这个问题搁置,而假设这些列是已知的。 例如示例数据中,可以先假设subject的数据[chinese,math]是已知的,这样问题就简化了许多 2 当已知了chinese,math后,我们至少要先得到转换后的tabel结构 如下; select stuid, 0 as chinese, 0 as math from dbo.StuScore 结果如下 stuid chinese math 3 0 0 3 0 0 4 0 0 5 0 0 5 0 0 6 0 0 7 0 0 8 0 0 8 0 0 3 接着就需要往这个数据集中去填充chinese, math的数据 select stuid, case subject when 'chinese' then score else 0 end as chinese, case subject when 'math' then score else 0 end as math from dbo.StuScore 结果如下: stuid chinese math 3 76.0 0.0 3 0.0 73.0 4 82.0 0.0 5 66.0 0.0 5 0.0 93.0 6 67.0 0.0 7 0.0 83.0 8 77.0 0.0 8 0.0 84.0 4 细心的读者会发现步骤3中的结果与我们想要的已经非常接近了,只需再做一个sum()处理,就OK了 select stuid, sum(case subject when 'chinese' then score else 0 end ) as chinese, sum(case subject when 'math' then score else 0 end ) as math from dbo.StuScore group by stuid 得到的正是我们想要的结果 stuid chinese math 3 76.0 73.0 4 82.0 0.0 5 66.0 93.0 6 67.0 0.0 7 0.0 83.0 8 77.0 84.0 是不是现在就已经完成了呢?答案是否定的。前面我们已经说过,是为了简化问题,在假设已经知道了subject数据的情况下,这么处理的,实际上subject的数据是可变的,未知的,接下来就是要解决这个问题了 5 要获取subject的数据其实很简单 select distinct subject from dbo.StuScore 获取以后怎样得到case subject when 'chinese' then score else 0 end 这种语句? 可以根据subject的值去动态的组sql语句 看下面的一段代码 declare @sql varchar(2000) set @sql='' select @sql =@sql+ ',case subject when '''+subject+''' then 1 else 0 end as ' + subject from (select distinct subject from dbo.StuScore) as sub print @sql message打印的信息如下: ,case subject when 'chinese' then 1 else 0 end as chinese,case subject when 'math' then 1 else 0 end as math 6 最后我们就需要将前面步骤综合起来,得到最终的sql declare @sql varchar(2000) set @sql='select stuid' select @sql =@sql+ ',sum(case subject when '''+subject+''' then score else 0 end) as ' + subject from (select distinct subject from dbo.StuScore) as sub set @sql=@sql + ' from dbo.StuScore group by stuid' exec(@sql) stuid chinese math 3 76.0 73.0 4 82.0 0.0 5 66.0 93.0 6 67.0 0.0 7 0.0 83.0 8 77.0 84.0 至此,整个分析过程和结果就都出来了。 初试写文章, 多包涵,指正。

学生成绩表(stuscore):

行转列问题总结

姓名:name

课程:subject

分数:score

学号:stuid

张三

数学

89

1

张三

语文

80

1

张三

英语

70

1

李四

数学

90

2

李四

语文

70

2

李四

英语

80

2

1、行转列

图片 1图片 2创建表 ANSI_NULLS ON
图片 3
图片 4GO
图片 5
图片 6SET QUOTED_IDENTIFIER ON
图片 7
图片 8GO
图片 9
图片 10SET ANSI_PADDING ON
图片 11
图片 12GO
图片 13
图片 14CREATE TABLE [dbo].[stuscore](
图片 15
图片 16    [name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
图片 17
图片 18    [subject] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
图片 19
图片 20    [score] [int] NULL,
图片 21
图片 22    [stuid] [int] NULL
图片 23
图片 24) ON [PRIMARY]
图片 25
图片 26 
图片 27
图片 28GO
图片 29
图片 30SET ANSI_PADDING OFF

---1、最简单的行转列
/*   

图片 31

问题:假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94

问题:

想变成(得到如下结果):
姓名 语文 数学 物理
李四 74   84   94
张三 74   83   93
*/
--测试用
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go

1.    计算每个人的总成绩并排名(要求显示字段:姓名,总成绩)

--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--通过动态构建@sql,得到如下脚本
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名

2.    计算每个人的总成绩并排名(要求显示字段: 学号,姓名,总成绩)

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
--得到SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b

3.    计算每个人单科的最高成绩(要求显示字段: 学号,姓名,课程,最高成绩)

--查询结果
/*
姓名         数学          物理          语文         

4.    计算每个人的平均成绩(要求显示字段: 学号,姓名,平均成绩)


5.    列出各门课程成绩最好的学生(要求显示字段: 学号,姓名,科目,成绩)

李四         84          94          74
张三         83          93          74

6.    列出各门课程成绩最好的两位学生(要求显示字段: 学号,姓名,科目,成绩)

(所影响的行数为 2 行)
*/

7.    统计如下:

--2 加合计
/*
问题:在上述结果的基础上加平均分,总分,得到如下结果:
姓名 语文 数学 物理 平均分 总分

学号

姓名

语文

数学

英语

总分

平均分


8.列出各门课程的平均成绩(要求显示字段:课程,平均成绩)

李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/

9.列出数学成绩的排名(要求显示字段:学号,姓名,成绩,排名)

--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名

10.列出数学成绩在2-3名的学生(要求显示字段:学号,姓名,科目,成绩)

--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)

11.求出李四的数学成绩的排名

--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名

12.统计如下:

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')

课程

不及格(0-59)个

良(60-80)个

优(81-100)个

其他实例

13.统计如下:数学:张三(50分),李四(90分),王五(90分),赵六(76分)

答案:

--3、不同数据按照序号转为列,方法基本同 1

1.       计算每个人的总成绩并排名

if object_id('tb1') is not null drop table tb1
go
CREATE table tb1 --数据表
(
cpici varchar(10) not null,
cname varchar(10) not null,
cvalue int null
)
--插入测试数据
INSERT INTO tb1 values('T501','x1',31)
INSERT INTO tb1 values('T501','x1',33)
INSERT INTO tb1 values('T501','x1',5)

图片 32select name,sum(score) as allscore from stuscore group by name order by allscore
图片 33
图片 34

INSERT INTO tb1 values('T502','x1',3)
INSERT INTO tb1 values('T502','x1',22)
INSERT INTO tb1 values('T502','x1',3)

  1.   计算每个人的总成绩并排名

INSERT INTO tb1 values('T503','x1',53)
INSERT INTO tb1 values('T503','x1',44)
INSERT INTO tb1 values('T503','x1',50)
INSERT INTO tb1 values('T503','x1',23)

图片 35select distinct t1.name,t1.stuid,t2.allscore from  stuscore t1,
图片 36
图片 37(
图片 38
图片 39    select stuid,sum(score) as allscore from stuscore group by stuid
图片 40
图片 41)t2
图片 42
图片 43where t1.stuid=t2.stuid
图片 44
图片 45order by t2.allscore desc
图片 46

--在sqlserver2000里需要用自增辅助
alter table tb1 add id int identity
go
declare @s varchar(8000)
set @s='select cpici '
select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) as cvlue'+ltrim(rn)
from (select distinct rn from (select rn=(select count(1) from tb1 where cpici=t.cpici and id<=t.id) from tb1 t)a)t
set @s=@s+' from (select rn=(select count(1) from tb1 where cpici=t.cpici and id<=t.id),* from tb1 t
) t group by cpici'

  1. 计算每个人单科的最高成绩

exec(@s)
go
alter table tb1 drop column id

图片 47 select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,
图片 48
图片 49(
图片 50
图片 51select stuid,max(score) as maxscore from stuscore group by stuid
图片 52
图片 53) t2
图片 54
图片 55where t1.stuid=t2.stuid and t1.score=t2.maxscore
图片 56

--再2005就可以用row_number
declare @s varchar(8000)
set @s='select cpici '
select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) as cvlue'+ltrim(rn)
from (select distinct rn from (select rn=row_number()over(partition by cpici order by getdate()) from tb1)a)t
set @s=@s+' from (select rn=row_number()over(partition by cpici order by getdate()),* from tb1
) t group by cpici'

本文由金沙澳门官网下载app发布于金沙澳门官网,转载请注明出处:行转列问题总结,4.    计算每个人的平均成绩

您可能还会对下面的文章感兴趣: