--假设表结构为,为方便说明问题,全部用中文名: create table 班级表(班级id int identity(1,1),班级名 varchar(10)) insert into 班级表 select '一班' union all select '二班'create table 学生表(学生id int identity(1,1),姓名 varchar(10)) insert into 学生表 select '张三' union all select '李四'create table 科目表(科目id int identity(1,1),科目 varchar(10)) insert into 科目表 select '语文' union all select '数学' union all select '英语' union all select '化学' union all select '物理'create table 成绩表(班级id int,学生id int,科目id int,学期 varchar(10),分数 decimal(20,2)) insert into 成绩表 select 1,1,1,'上学期',100 union all select 1,1,2,'上学期',90 union all select 1,1,1,'下学期',90 union all select 1,1,2,'下学期',92 union all select 1,2,2,'上学期',90 union all select 1,2,1,'下学期',90 union all select 1,2,2,'下学期',92go--则可以用下面的存储过程来处理 create proc p_qry @班级名 varchar(10) --要查询的班级 as declare @班级id int select @班级id=班级id from 班级表 where 班级名=@班级名declare @s varchar(8000),@tb varchar(8000) set @s='' select @s=@s+',['+姓名+'_上学期]=sum(case when 姓名='''+姓名+''' and 学期=''上学期'' then 分数 end)' +',['+姓名+'_下学期]=sum(case when 姓名='''+姓名+''' and 学期=''下学期'' then 分数 end)' from(select distinct a.姓名 from 学生表 a join 成绩表 b on a.学生id=b.学生id where b.班级id=@班级id)a set @tb=' select a.*,b.科目,c.姓名 from 成绩表 a join 科目表 b on a.科目id=b.科目id join 学生表 c on a.学生id=c.学生id where a.班级id='+cast(@班级id as varchar) exec('select 科目'+@s+' from('+@tb+') a group by 科目') go--调用示例 exec p_qry '一班' go--删除测试环境 drop table 班级表,学生表,科目表,成绩表 drop proc p_qry
测试: create table t1 (classname varchar(10),name varchar(10),km varchar(10),shan int,xia int) insert t1 values('cls1','学生1','语文',50,55) insert t1 values('cls1','学生1','数学',60,65) insert t1 values('cls1','学生2','语文',70,75) insert t1 values('cls1','学生2','数学',80,85) insert t1 values('cls2','学生3','语文',50,55) insert t1 values('cls2','学生3','外语',60,65) insert t1 values('cls2','学生4','语文',70,75) insert t1 values('cls2','学生4','外语',80,85) go create procedure procName @clsName varchar(10) as declare @sql varchar(8000) set @sql = 'select km' select @sql = @sql + ',sum(case [name] when '''+[name]+''' then shan end) ['+[name]+'上],sum(case [name] when '''+[name]+''' then xia end) ['+[name]+'下]' --select @sql = @sql + ',sum(case km when '''+km+''' then xia end) ['+km+'下]' from (select distinct [name] from t1 where classname = @clsName) as a select @sql = @sql+' from t1 where classname = '''+ @clsName +''' group by km' exec(@sql) go--调用存储过程 exec procName 'cls1' /* 显示结果 km 学生1上 学生1下 学生2上 学生2下 ---------- ----------- ----------- ----------- ----------- 数学 60 65 80 85 语文 50 55 70 75 */ exec procName 'cls2' /* 显示结果 km 学生3上 学生3下 学生4上 学生4下 ---------- ----------- ----------- ----------- ----------- 外语 60 65 80 85 语文 50 55 70 75 */
et it and see it slowly! :-)
/*--表结构不清晰.假设表结构为: 科目 学生1_上学期 学生1_下学期 .... --*/--处理的存储过程 create proc p_qry @pagesize int --每页要显示的学生记录数 as declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000) ,@i int,@j int select @s1='',@s2='',@s3=''select gid=cast((colid-2)/@pagesize/2 as varchar),name into #t from syscolumns where object_id('表A')=id and name<>'科目' order by colid set @j=@pagesize-((@@rowcount/2) % @pagesize)select @s1=case @i when gid then @s1 else @s1+',@'+gid+' varchar(8000)' end ,@s2=@s2+case @i when gid then '' else ' from 表A'',@'+gid+'=''select 页号='+gid+',科目' end+',['+name+']' ,@s3=case @i when gid then @s3 else @s3+'+'' union all ''+@'+gid end ,@i=gid from #twhile @j>0 select @s2=@s2+',null,null',@j=@j-1select @s1=substring(@s1,2,8000) ,@s2=substring(@s2,11,8000)+' from 表A''' ,@s3='exec('+substring(@s3,16,8000)+')'exec('declare '+@s1+' select '+@s2+' '+@s3) go--调用 exec p_qry 2
--下面是例子:/*--假设有数据科目 学生1_上学期 学生1_下学期 学生2_上学期 学生2_下学期 学生3_上学期 学生3_下学期 学生4_上学期 学生4_下学期 学生5_上学期 学生5_下学期 ------ ------------ ----------- ----------- ------------ ------------ ------------ ------------ ----------- ------------ ----------- 语文 80 90 30 69 87 65 60 60 87 65 数学 45 43 43 98 0 5 70 70 0 5 现要需要一个存储过程,输入每页的学生数目,得到结果,假设输入2,得到结果: 页号 科目 学生1_上学期 学生1_下学期 学生2_上学期 学生2_下学期 ----------- ---------- ----------- ----------- ----------- ----------- 0 语文 80 90 30 69 0 数学 45 43 43 98 1 语文 87 65 60 60 1 数学 0 5 70 70 2 语文 87 65 NULL NULL 2 数学 0 5 NULL NULL(所影响的行数为 6 行)--*/ create table 表A(科目 varchar(10) ,学生1_上学期 int,学生1_下学期 int ,学生2_上学期 int,学生2_下学期 int ,学生3_上学期 int,学生3_下学期 int ,学生4_上学期 int,学生4_下学期 int ,学生5_上学期 int,学生5_下学期 int) insert into 表A select '语文',80,90,30,69,87,65,60,60,87,65 union all select '数学',45,43,43,98,0,5,70,70,0,5 go/*--表结构不清晰.假设表结构为: 科目 学生1_上学期 学生1_下学期 .... --*/--处理的存储过程 create proc p_qry @pagesize int --每页要显示的学生记录数 as declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000) ,@i int,@j int select @s1='',@s2='',@s3=''select gid=cast((colid-2)/@pagesize/2 as varchar),name into #t from syscolumns where object_id('表A')=id and name<>'科目' order by colid set @j=@pagesize-((@@rowcount/2) % @pagesize)select @s1=case @i when gid then @s1 else @s1+',@'+gid+' varchar(8000)' end ,@s2=@s2+case @i when gid then '' else ' from 表A'',@'+gid+'=''select 页号='+gid+',科目' end+',['+name+']' ,@s3=case @i when gid then @s3 else @s3+'+'' union all ''+@'+gid end ,@i=gid from #twhile @j>0 select @s2=@s2+',null,null',@j=@j-1select @s1=substring(@s1,2,8000) ,@s2=substring(@s2,11,8000)+' from 表A''' ,@s3='exec('+substring(@s3,16,8000)+')'exec('declare '+@s1+' select '+@s2+' '+@s3) go--调用 exec p_qry 2 go--测试测试环境 drop table 表A drop proc p_qry
[交流]行列转换
建议如下:
A表:studentinfo (stu_id,name,sex,class_id....)
B : class(class_id,classname)
C: course(course_id ,coursename,xueqi)
d: score(stu_id,course_id,score)
建立一个视图view1(name,coursename,xueqi,score)
这样才行。
在此进行一个行列转换就行了。
B表字段:班级classname 姓名name 科目km 上学期分数shan 下学期分数xia
C表有该学校所有的课本名也就是科目。这个主要是用于
select distinct km(科目)from c表
A表没有什么用。因为你可以随便指定一个条件的班级
create table 班级表(班级id int identity(1,1),班级名 varchar(10))
insert into 班级表
select '一班'
union all select '二班'create table 学生表(学生id int identity(1,1),姓名 varchar(10))
insert into 学生表
select '张三'
union all select '李四'create table 科目表(科目id int identity(1,1),科目 varchar(10))
insert into 科目表
select '语文'
union all select '数学'
union all select '英语'
union all select '化学'
union all select '物理'create table 成绩表(班级id int,学生id int,科目id int,学期 varchar(10),分数 decimal(20,2))
insert into 成绩表
select 1,1,1,'上学期',100
union all select 1,1,2,'上学期',90
union all select 1,1,1,'下学期',90
union all select 1,1,2,'下学期',92
union all select 1,2,2,'上学期',90
union all select 1,2,1,'下学期',90
union all select 1,2,2,'下学期',92go--则可以用下面的存储过程来处理
create proc p_qry
@班级名 varchar(10) --要查询的班级
as
declare @班级id int
select @班级id=班级id from 班级表 where 班级名=@班级名declare @s varchar(8000),@tb varchar(8000)
set @s=''
select @s=@s+',['+姓名+'_上学期]=sum(case when 姓名='''+姓名+''' and 学期=''上学期'' then 分数 end)'
+',['+姓名+'_下学期]=sum(case when 姓名='''+姓名+''' and 学期=''下学期'' then 分数 end)'
from(select distinct a.姓名 from 学生表 a join 成绩表 b on a.学生id=b.学生id where b.班级id=@班级id)a
set @tb='
select a.*,b.科目,c.姓名
from 成绩表 a
join 科目表 b on a.科目id=b.科目id
join 学生表 c on a.学生id=c.学生id
where a.班级id='+cast(@班级id as varchar)
exec('select 科目'+@s+' from('+@tb+') a group by 科目')
go--调用示例
exec p_qry '一班'
go--删除测试环境
drop table 班级表,学生表,科目表,成绩表
drop proc p_qry
create table t1 (classname varchar(10),name varchar(10),km varchar(10),shan int,xia int)
insert t1 values('cls1','学生1','语文',50,55)
insert t1 values('cls1','学生1','数学',60,65)
insert t1 values('cls1','学生2','语文',70,75)
insert t1 values('cls1','学生2','数学',80,85)
insert t1 values('cls2','学生3','语文',50,55)
insert t1 values('cls2','学生3','外语',60,65)
insert t1 values('cls2','学生4','语文',70,75)
insert t1 values('cls2','学生4','外语',80,85)
go
create procedure procName @clsName varchar(10)
as
declare @sql varchar(8000)
set @sql = 'select km'
select @sql = @sql + ',sum(case [name] when '''+[name]+''' then shan end) ['+[name]+'上],sum(case [name] when '''+[name]+''' then xia end) ['+[name]+'下]'
--select @sql = @sql + ',sum(case km when '''+km+''' then xia end) ['+km+'下]'
from (select distinct [name] from t1 where classname = @clsName) as a
select @sql = @sql+' from t1 where classname = '''+ @clsName +''' group by km'
exec(@sql)
go--调用存储过程
exec procName 'cls1'
/* 显示结果
km 学生1上 学生1下 学生2上 学生2下
---------- ----------- ----------- ----------- -----------
数学 60 65 80 85
语文 50 55 70 75
*/
exec procName 'cls2'
/* 显示结果
km 学生3上 学生3下 学生4上 学生4下
---------- ----------- ----------- ----------- -----------
外语 60 65 80 85
语文 50 55 70 75
*/
科目 学生1_上学期 学生1_下学期 ....
--*/--处理的存储过程
create proc p_qry
@pagesize int --每页要显示的学生记录数
as
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
,@i int,@j int
select @s1='',@s2='',@s3=''select gid=cast((colid-2)/@pagesize/2 as varchar),name into #t
from syscolumns
where object_id('表A')=id and name<>'科目' order by colid
set @j=@pagesize-((@@rowcount/2) % @pagesize)select @s1=case @i when gid then @s1 else @s1+',@'+gid+' varchar(8000)' end
,@s2=@s2+case @i when gid then '' else ' from 表A'',@'+gid+'=''select 页号='+gid+',科目' end+',['+name+']'
,@s3=case @i when gid then @s3 else @s3+'+'' union all ''+@'+gid end
,@i=gid
from #twhile @j>0
select @s2=@s2+',null,null',@j=@j-1select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,11,8000)+' from 表A'''
,@s3='exec('+substring(@s3,16,8000)+')'exec('declare '+@s1+'
select '+@s2+'
'+@s3)
go--调用
exec p_qry 2
------ ------------ ----------- ----------- ------------ ------------ ------------ ------------ ----------- ------------ -----------
语文 80 90 30 69 87 65 60 60 87 65
数学 45 43 43 98 0 5 70 70 0 5
现要需要一个存储过程,输入每页的学生数目,得到结果,假设输入2,得到结果:
页号 科目 学生1_上学期 学生1_下学期 学生2_上学期 学生2_下学期
----------- ---------- ----------- ----------- ----------- -----------
0 语文 80 90 30 69
0 数学 45 43 43 98
1 语文 87 65 60 60
1 数学 0 5 70 70
2 语文 87 65 NULL NULL
2 数学 0 5 NULL NULL(所影响的行数为 6 行)--*/
create table 表A(科目 varchar(10)
,学生1_上学期 int,学生1_下学期 int
,学生2_上学期 int,学生2_下学期 int
,学生3_上学期 int,学生3_下学期 int
,学生4_上学期 int,学生4_下学期 int
,学生5_上学期 int,学生5_下学期 int)
insert into 表A
select '语文',80,90,30,69,87,65,60,60,87,65
union all select '数学',45,43,43,98,0,5,70,70,0,5
go/*--表结构不清晰.假设表结构为:
科目 学生1_上学期 学生1_下学期 ....
--*/--处理的存储过程
create proc p_qry
@pagesize int --每页要显示的学生记录数
as
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
,@i int,@j int
select @s1='',@s2='',@s3=''select gid=cast((colid-2)/@pagesize/2 as varchar),name into #t
from syscolumns
where object_id('表A')=id and name<>'科目' order by colid
set @j=@pagesize-((@@rowcount/2) % @pagesize)select @s1=case @i when gid then @s1 else @s1+',@'+gid+' varchar(8000)' end
,@s2=@s2+case @i when gid then '' else ' from 表A'',@'+gid+'=''select 页号='+gid+',科目' end+',['+name+']'
,@s3=case @i when gid then @s3 else @s3+'+'' union all ''+@'+gid end
,@i=gid
from #twhile @j>0
select @s2=@s2+',null,null',@j=@j-1select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,11,8000)+' from 表A'''
,@s3='exec('+substring(@s3,16,8000)+')'exec('declare '+@s1+'
select '+@s2+'
'+@s3)
go--调用
exec p_qry 2
go--测试测试环境
drop table 表A
drop proc p_qry