请教一个动态实现的写法.小菜我实在无能力现实这个效果,特求高手给写个例子啊------------------------------------
月份 老师 学生 科目 成绩2 李导 张三 语文 60
2 李导 张三 数学 70
2 李导 李四 数学 70
2 王伟 李四 语文 50
2 王伟 李四 体育 80
2 王伟 张三 语文 65
.
.
.
-------------------------------------
老师和学生都是动态的很多数据,我想用户在查询的时候用月份做条件查询得到如下:
在同一个月份的条件,1:学生转列,唯一; 2:所有老师都转成行; 3:合计老师对于学生的成绩总和. -------------------------------------
学生(唯一) 李导(对于张三成绩的和) 王伟
张三 130 65
李四 70 130
.
.
.
月份 老师 学生 科目 成绩2 李导 张三 语文 60
2 李导 张三 数学 70
2 李导 李四 数学 70
2 王伟 李四 语文 50
2 王伟 李四 体育 80
2 王伟 张三 语文 65
.
.
.
-------------------------------------
老师和学生都是动态的很多数据,我想用户在查询的时候用月份做条件查询得到如下:
在同一个月份的条件,1:学生转列,唯一; 2:所有老师都转成行; 3:合计老师对于学生的成绩总和. -------------------------------------
学生(唯一) 李导(对于张三成绩的和) 王伟
张三 130 65
李四 70 130
.
.
.
[url=http://][/url]
/*
标题:普通行列转换(version 2.0)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:-03-09
地点:广东深圳
说明:普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法,version 2.0增加sql server 2005的有关写法。问题:假设有张学生成绩表(tb)如下:
姓名课程分数
张三语文74
张三数学83
张三物理93
李四语文74
李四数学84
李四物理94
想变成(得到如下结果):
姓名语文数学物理
---- ---- ---- ----
李四74 84 94
张三74 83 93
-------------------
*/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--SQL SERVER 2000 静态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 姓名--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 SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程in (语文,数学,物理)) b--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')---------------------------------/*
问题:在上述结果的基础上加平均分,总分,得到如下结果:
姓名语文数学物理平均分总分
---- ---- ---- ---- ------ ----
李四74 84 94 84.00 252
张三74 83 93 83.33 250
*/--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 姓名--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) --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.姓名--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.姓名')drop table tb ------------------
------------------/*
问题:如果上述两表互相换一下:即表结构和数据为:
姓名语文数学物理
张三74
李四74
想变成(得到如下结果):
姓名课程分数
---- ---- ----
李四语文74
李四数学84
李四物理94
张三语文74
张三数学83
张三物理93
--------------
*/create table tb(姓名varchar(10) , 语文int , 数学int , 物理int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名, 课程= '语文' , 分数= 语文from tb
union all
select 姓名, 课程= '数学' , 分数= 数学from tb
union all
select 姓名, 课程= '物理' , 分数= 物理from tb
) t
order by 姓名, case 课程when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名, [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名')--SQL SERVER 2005 动态SQL。
select 姓名, 课程, 分数from tb unpivot (分数for 课程in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。--------------------
/*
问题:在上述的结果上加个平均分,总分,得到如下结果:
姓名课程 分数
---- ------ ------
李四语文 74.00
李四数学 84.00
李四物理 94.00
李四平均分84.00
李四总分 252.00
张三语文 74.00
张三数学 83.00
张三物理 93.00
张三平均分83.33
张三总分 250.00
------------------
*/select * from
(
select 姓名as 姓名, 课程= '语文' , 分数= 语文from tb
union all
select 姓名as 姓名, 课程= '数学' , 分数= 数学from tb
union all
select 姓名as 姓名, 课程= '物理' , 分数= 物理from tb
union all
select 姓名as 姓名, 课程= '平均分' , 分数= cast((语文+ 数学+ 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名as 姓名, 课程= '总分' , 分数= 语文+ 数学+ 物理from tb
) t
order by 姓名, case 课程when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb
看动态的就ok了。
李导=sum(case when 老师='李导' then 成绩 else 0 end),
王伟=sum(case when 老师='王伟' then 成绩 else 0 end)
from tb
group by 学生
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-11-04 10:28:03
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([月份] int,[老师] varchar(4),[学生] varchar(4),[科目] varchar(4),[成绩] int)
insert [tb]
select 2,'李导','张三','语文',60 union all
select 2,'李导','张三','数学',70 union all
select 2,'李导','李四','数学',70 union all
select 2,'王伟','李四','语文',50 union all
select 2,'王伟','李四','体育',80 union all
select 2,'王伟','张三','语文',65
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select 学生 '
select @sql = @sql + ' , sum(case 老师 when ''' + 老师 + ''' then 成绩 else 0 end) [' +老师 + ']'
from (select distinct 老师 from tb) as a
set @sql = @sql + ' from tb group by 学生'
exec(@sql)
----------------结果----------------------------
/* 学生 李导 王伟
---- ----------- -----------
李四 70 130
张三 130 65(2 行受影响)*/
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([月份] int,[老师] nvarchar(2),[学生] nvarchar(2),[科目] nvarchar(2),[成绩] int)
Insert #T
select 2,N'李导',N'张三',N'语文',60 union all
select 2,N'李导',N'张三',N'数学',70 union all
select 2,N'李导',N'李四',N'数学',70 union all
select 2,N'王伟',N'李四',N'语文',50 union all
select 2,N'王伟',N'李四',N'体育',80 union all
select 2,N'王伟',N'张三',N'语文',65
Go
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([老师])+N'=sum(case when [老师]=N'+quotename([老师],'''')+N' then [成绩] else 0 end)'from #T group by [老师]
--顯示生成語句
print N'select [学生]'+@s+N' from #T group by [学生]'
exec(N'select [学生]'+@s+N' from #T group by [学生]')
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(老师)+'=sum(case when 老师='+quotename(老师,'''')+' then 成绩 else 0 end)'
from tb
exec('select 学生'+@s+' from tb group by 学生')
Insert into tb
select 2,'李导','张三','语文',60 union all
select 2,'李导','张三','数学',70 union all
select 2,'李导','李四','数学',70 union all
select 2,'王伟','李四','语文',50 union all
select 2,'王伟','李四','体育',80 union all
select 2,'王伟','张三','语文',65
go
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(老师)+'=sum(case when 老师='+quotename(老师,'''')+' then 成绩 else 0 end)'
from (select distinct 老师 from tb)t
exec('select 学生'+@s+' from tb group by 学生')
/*
学生 李导 王伟
---- ----------- -----------
李四 70 130
张三 130 65(2 行受影响)*/
go
drop table tb
set @s=''
Select @s=@s+','+quotename(老师)+'=sum(case when 老师='+quotename(老师,'''')+' then 成绩 else 0 end)'
from tb
exec('select 学生'+@s+' from tb group by 学生')
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
月份 int,
老师 varchar(10),
学生 varchar(10),
科目 varchar(10),
成绩 int
)
go
insert into tb
select 2,'李导','张三','语文',60 union all
select 2,'李导','张三','数学',70 union all
select 2,'李导','李四','数学',70 union all
select 2,'王伟','李四','语文',50 union all
select 2,'王伟','李四','体育',80 union all
select 2,'王伟','张三','语文',65
go
--静态
select 学生,
李导=sum(case when 老师='李导' then 成绩 else 0 end),
王伟=sum(case when 老师='王伟' then 成绩 else 0 end)
from tb group by 学生
go
--动态
declare @sql varchar(max)
set @sql='select 学生'
select @sql=@sql+',['+老师+']=sum(case when 老师='''+老师+''' then 成绩 else 0 end)' from tb group by 老师
select @sql=@sql+' from tb group by 学生'
exec(@sql)
(
教师 varchar(10),
姓名 varchar(20),
课程 varchar(10),
分数 int
)
insert into tb values('李导','张三','语文',60)
insert into tb values('李导','张三','数学',70)
insert into tb values('李导','李四','数学',70)
insert into tb values('王伟','李四','语文',50)
insert into tb values('王伟','李四','体育',80)
insert into tb values('王伟','张三','语文',65)
--静态方法
select 姓名
sum(case when 教师='李导' then 分数 else 0 end) as 李导
sum(case when 教师='王伟' then 分数 else 0 end) as 王伟
from tb Group by 姓名
--动态方法
declare @sql varchar(8000)
set @sql='select 学生'
select @sql=@sql+ ',sum(case when 教师='''+教师+''' then 分数 else 0 end )['+教师+']'
from (select distinct 教师 from tb) as a
select @sql=@sql+ ' from tb Group by 姓名 order by 姓名 desc'
exec(@sql)