随笔档案(496)

怎么样用left join 把行改成列

怎么样用left join 把行改成列
有如下表a1

学号     姓名   课程类型   学分数
2001   李四     公共课         2
2001   李四     专业课         1
2001   李四     公共课         3
2001   李四     专业课         2

通过查询器查询成
学号         姓名     公共课学分数   专业课学分数
2001       李四           5                       3

用下面语句
select   学号,姓名,sum(学分数)   as   公共课学分数   from   A1   where   课程类型='公共课'   group   by   学号
left   join   .......
后面怎么写呢,请指教.要不要建个临时表. 20 回复次数：6

第1个回答

SQL code

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

普通行列转换

(爱新觉罗.毓华 2007-11-18于海南三亚)

假设有张学生成绩表(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只有语文、数学、物理这三门课程。

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) 物理

from tb

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 Subject when ''' + 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

) t

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 ) t order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end drop table tb1

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

第2个回答
有如下表a1  

学号           姓名       课程类型       学分数  
2001       李四           公共课                   2  
2001       李四           专业课                   1  
2001       李四           公共课                   3  
2001       李四           专业课                   2  

通过查询器查询成  
学号                   姓名           公共课学分数       专业课学分数  
2001               李四                       5                                               3  

用下面语句  
select       学号,姓名,sum(学分数)       as       公共课学分数       from       A1       where       课程类型='公共课'       group       by       学号  
left       join       .......  
后面怎么写呢,请指教.要不要建个临时表.  

SQL code

--------------------------------------------------------------------------------select 学号,姓名,

sum(case 课程类型 when '公共课' then 学分数 else 0 end) 公共课学分数,

sum(case 课程类型 when '专业课' then 学分数 else 0 end) 专业课学分数

from tb

group by 学号,姓名

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

第3个回答

SQL code

----------------------------------------------------------------------------------静态SQL,指课程类型只有公共课,专业课

select 学号,姓名,

sum(case 课程类型 when '公共课' then 学分数 else 0 end) 公共课学分数,

sum(case 课程类型 when '专业课' then 学分数 else 0 end) 专业课学分数

from tb

group by 学号,姓名

--静态SQL,指课程类型不止公共课,专业课

declare @sql varchar(8000)

set @sql = 'select 学号,姓名'

select @sql = @sql1 + ' , sum(case 课程类型 when ''' + 课程类型 + ''' then 学分数 else 0 end) [' + 课程类型 + '学分数]'

from (select distinct 课程类型 from tb) as a

set @sql = @sql + ' from tb group by 学号,姓名'

exec(@sql)

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

第4个回答

SQL code

--------------------------------------------------------------------------------create table tb(学号 int,姓名 varchar(10),课程类型 varchar(10),学分数 int)

insert tb select 2001,'李四','公共课',2

union all select 2001,'李四','专业课',1

union all select 2001,'李四','公共课',3

union all select 2001,'李四','专业课',2

select 学号,姓名,

       公共课=sum(case 课程类型 when '公共课' then 学分数 else 0 end),

       专业课=sum(case 课程类型 when '专业课' then 学分数 else 0 end)

from tb

group by 学号,姓名

--动态SQL

declare @sql varchar(4000)

select @sql='select 学号,姓名'

select @sql=@sql+',['+课程类型+']=sum(case 课程类型 when '''+课程类型+''' then 学分数 else 0 end)'

from tb group by 课程类型

exec(@sql+N' from tb group by 学号,姓名')

drop table tb

/*

学号          姓名         公共课         专业课        

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

2001        李四         5           3

*/

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

第5个回答
select     学号,姓名,
sum(decode(课程类型,'公共课',   学分数,   0)   公共课学分数,
sum(decode(课程类型,   '专业课',学分数,   0)   专业课学分数
from   tb
group   by   学号,姓名