SQL系列交叉报表

时间:2022-11-20 04:50:57

SQL系列交叉报表

 

 

 

SQL完成交叉表功能2011-11-24 14:39:00  

假设有张学生成绩表(tb)如下:

 

Name Subject Result

 张三 语文  74

 张三 数学  83

 张三 物理  93

 李四 语文  74

 李四 数学  84

 李四 物理  94

 

 

想变成

 姓名         语文       数学        物理        

/* -------------------------------------------------------------- ----------- -----------*/

 李四         74          84          94

张三         74          83          93

 

create table tb

(

  Name    varchar(10) ,

  Subject varchar(10) ,

  Result  int

 )

 

insert into tb(Name , Subject , Result)values('张三' , '语文' , 74)

insert into tb(Name , Subject , Result)values('张三' , '数学' , 83)

insert into tb(Name , Subject , Result)values('张三' , '物理' , 93)

insert into tb(Name , Subject , Result)values('李四' , '语文' , 74)

insert into tb(Name , Subject , Result)values('李四' , '数学' , 84)

insert into tb(Name , Subject , Result)values('李四' , '物理' , 94)

go

 

 

--静态SQL,指subject只有语文、数学、物理这三门课程。

 selectname 姓名,

  max(case subject when '语文' then result else 0 end) 语文,

  max(case subject when '数学' then result else 0 end) 数学,

   max(case subject when '物理' then result else 0end) 物理

 fromtb

 group by name

 /*

 姓名         语文       数学        物理        

 ---------- ----------- ----------- -----------

 李四         74          84          94

 张三         74          83          93

 */

 

--动态SQL,指subject不止语文、数学、物理这三门课程。

 declare @sql varchar(8000)

 set@sql = 'select Name as ' + '姓名'

 select @sql = @sql + ' , max(case Subject when''' + Subject + ''' then Result else 0 end) [' + Subject + ']'

 from(select distinct Subject from tb) as a

 set@sql = @sql + ' from tb group by name'

 exec(@sql)

 /*

 姓名         数学       物理        语文        

 ---------- ----------- ----------- --------------------------------------------------------

 李四         84          94          74

 张三         83          93          74

 */

 

/*加个平均分,总分

 

姓名         语文        数学       物理        平均分       总分        

 ---------- ----------- ----------- ------------------------------- ----------- ------------------------------

 李四         74          84          94          84.00           252

张三         74          83          93          83.33            250

 

*/

 

 --静态SQL,指subject只有语文、数学、物理这三门课程。

select name 姓名,

 max(case subject when '语文' then result else 0 end) 语文,

 max(case subject when '数学' then result else 0 end) 数学,

 max(case subject when '物理' then result else 0 end) 物理,

 cast(avg(result*1.0) as decimal(18,2)) 平均分,

 sum(result) 总分

from tb

group by name

/*

姓名         语文        数学       物理        平均分       总分        

 ---------- ----------- ----------- ------------------------------- ------------------------------------------

 李四         74          84          94          84.00        252

 张三         74          83          93          83.33        250

 */

  

--动态SQL,指subject不止语文、数学、物理这三门课程。

declare @sql1 varchar(8000)

set @sql1 = 'select Name as ' + '姓名'

select @sql1 = @sql1 + ' , max(case Subjectwhen ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'

from (select distinct Subject from tb) as a

set @sql1 = @sql1 + ' , cast(avg(result*1.0)as decimal(18,2)) 平均分,sum(result) 总分from tb group by name'

exec(@sql1)

 

/*

 

姓名         数学        物理       语文        平均分        总分        

 ---------- ----------- ----------- ------------------------------- ------------------------------------------

 李四         84          94          74          84.00         252

 张三         83          93          74          83.33         250

 */

 

drop table tb  

 

 

/*

 

如果上述两表互相换一下:即

 

 

 

姓名 语文 数学 物理

 

张三74  83  93

 

李四74  84  94

 

 

 

想变成

 

Name      Subject Result    

 ----------------------------------------- -----------

 李四         语文     74

 李四         数学     84

 李四         物理     94

 张三         语文     74

 张三         数学     83

 张三         物理     93

 */

 

 

 

create table tb1

 

(

 

   姓名varchar(10) ,

   语文int ,

   数学int ,

   物理int

 )

 

 

insert into tb1(姓名, 语文, 数学, 物理) values('张三',74,83,93)

insert into tb1(姓名, 语文, 数学, 物理) values('李四',74,84,94)

 

select * from

(

 select 姓名as Name , Subject = '语文' , Result = 语文from tb1

 union all

 select 姓名as Name , Subject = '数学' , Result = 数学from tb1

 union all

 select 姓名as Name , Subject = '物理' , Result = 物理from tb1

)

order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end

 

 

 

/*加个平均分,总分

 

Name      Subject     Result             

--------------- -------------------------------------

 

李四         语文      74.00

李四         数学      84.00

李四         物理      94.00

李四         平均分    84.00

李四         总分      252.00

张三         语文      74.00

张三         数学      83.00

张三         物理      93.00

张三         平均分    83.33

张三         总分      250.00

*/

 

select * from

 

(

 select 姓名as Name , Subject = '语文' , Result = 语文from tb1

 union all

 select 姓名as Name , Subject = '数学' , Result = 数学from tb1

 union all

 select姓名as Name ,Subject = '物理' , Result = 物理from tb1

 union all

  select 姓名as Name , Subject = '平均分' , Result = cast((语文+ 数学+ 物理)*1.0/3 as decimal(18,2)) from tb1

  union all

 select 姓名as Name , Subject = '总分' , Result = 语文+ 数学+ 物理from tb1

 )

order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end

 droptable tb1