经典连接查询----元旦送大礼 散分

Question

转老乌龟的. 
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 enddrop table tb1

Answer 1

create table tb(code1 varchar(10),code2 varchar(10),code3 varchar(10))insert tb select        '01151',               'A19'        ,         '01' 
insert tb select        '01151' ,              'A19'       ,          '02' 
insert tb select        '01151'  ,             'A19'      ,           '03' 
insert tb select        '01M75'   ,            'C07'     ,            '01' 
insert tb select        '01M75'    ,           'C07'    ,             '02' 
insert tb select        '01M75'     ,          'C07'   ,              '03' 
insert tb select        '01C14'      ,         'K93'  ,               '01' 
insert tb select        '01C14'       ,        'K93' ,                '02' 
insert tb select        '01C14'        ,       'K93',                 '03'create table item(idcode varchar(10),idname varchar(20))
insert item select '01','你好'
insert item select '02','谢谢'
insert item select '03','学习'select  a.code1,a.code2,   
        max(case  a.code3  when  '01' then  a.code3  else ''  end) as f1,
        max(case  b.idcode when  '01' then  b.idname else '' end) as idname1,  
        max(case  a.code3  when  '02' then  a.code3  else ''  end) as f2,
        max(case  b.idcode when  '02' then  b.idname else '' end) as idname2,   
        max(case  a.code3  when  '03' then  a.code3  else ''  end) as f3,
        max(case  b.idcode when  '03' then  b.idname else '' end) as idname3       
from  tb a,item b
where a.code3=b.idcode
group  by  code1,code2   drop table tb,item/*
code1      code2      f1         idname1       f2         idname2      f3  idname3              
---------- ---------- ---------- -------------------- ---------- -------------------- ---------
01151      A19        01         你好                   02         谢谢       03         学习
01M75      C07        01         你好                   02         谢谢       03         学习
01C14      K93        01         你好                   02         谢谢       03         学习（所影响的行数为 3 行）
*/

Answer 2

declare @tab table(code1 varchar(5),code2 varchar(3),code3 varchar(3))
insert @tab select '01151','A19','01'
insert @tab select '01151','A19','02'
insert @tab select '01151','A19','03'
insert @tab select '01M75','C07','01'   
insert @tab select '01M75','C07','02'   
insert @tab select '01M75','C07','03'   
insert @tab select '01C14','K93','01'   
insert @tab select '01C14','K93','02'   
insert @tab select '01C14','K93','03'  declare @item table(idcode varchar(3),idname varchar(4))
insert @item select '01','你好'
insert @item select '02','谢谢'   
insert @item select '03','学习'select     code1,code2, 
    max(case   code3     when   '01'   then   code3     else   ''   end)     as     f1, 
    max(case   code3     when   '01'   then   b.idname else '' end ) idName1,
    max(case   code3     when   '02'   then   code3     else   ''   end)     as     f2,
    max(case   code3     when   '02'   then   b.idname else '' end ) idName2 , 
    max(case   code3     when   '03'   then   code3     else   ''   end)     as     f3  ,
    max(case   code3     when   '03'   then   b.idname else '' end )  idName3 
  from   @tab a
left join @item b on  a.code3 = b.idcode
  group   by   code1,code2 
/*code1 code2 f1   idName1 f2   idName2 f3   idName3 
----- ----- ---- ------- ---- ------- ---- ------- 
01151 A19   01   你好      02   谢谢      03   学习
01M75 C07   01   你好      02   谢谢      03   学习
01C14 K93   01   你好      02   谢谢      03   学习（所影响的行数为 3 行）
*/

Answer 3

declare @tab table(code1 varchar(5),code2 varchar(3),code3 varchar(3))
insert @tab select '01151','A19','01'
insert @tab select '01151','A19','02'
insert @tab select '01151','A19','03'
insert @tab select '01M75','C07','01'   
insert @tab select '01M75','C07','02'   
insert @tab select '01M75','C07','03'   
insert @tab select '01C14','K93','01'   
insert @tab select '01C14','K93','02'   
insert @tab select '01C14','K93','03'  declare @item table(idcode varchar(3),idname varchar(4))
insert @item select '01','你好'
insert @item select '02','谢谢'   
insert @item select '03','学习'select     code1,code2, 
    max(case   code3     when   '01'   then   code3     else   ''   end)     as     f1, 
    max(case   code3     when   '01'   then   b.idname else '' end ) idName1,
    max(case   code3     when   '02'   then   code3     else   ''   end)     as     f2,
    max(case   code3     when   '02'   then   b.idname else '' end ) idName2 , 
    max(case   code3     when   '03'   then   code3     else   ''   end)     as     f3  ,
    max(case   code3     when   '03'   then   b.idname else '' end )  idName3 
  from   @tab a
left join @item b on  a.code3 = b.idcode
  group   by   code1,code2 
/*code1 code2 f1   idName1 f2   idName2 f3   idName3 
----- ----- ---- ------- ---- ------- ---- ------- 
01151 A19   01   你好      02   谢谢      03   学习
01M75 C07   01   你好      02   谢谢      03   学习
01C14 K93   01   你好      02   谢谢      03   学习（所影响的行数为 3 行）
*/

Answer 4

create table tb(code1 varchar(10),code2 varchar(10),code3 varchar(10))
insert tb select '01151' , 'A19' , '01' 
insert tb select '01151' , 'A19' , '02' 
insert tb select '01151' , 'A19' , '03' 
insert tb select '01M75' , 'C07' , '01' 
insert tb select '01M75' , 'C07' , '02' 
insert tb select '01M75' , 'C07' , '03' 
insert tb select '01C14' , 'K93' , '01' 
insert tb select '01C14' , 'K93' , '02' 
insert tb select '01C14' , 'K93' , '03'
create table item(idcode varchar(10),idname varchar(20))
insert item select '01','你好'
insert item select '02','谢谢'
insert item select '03','学习'
goselect a.code1,a.code2,
  max(case code3 when '01' then a.code3 else '' end) f1,
  max(case code3 when '01' then b.idname else '' end) idname1,
  max(case code3 when '02' then a.code3 else '' end) f2,
  max(case code3 when '02' then b.idname else '' end) idname2,
  max(case code3 when '03' then a.code3 else '' end) f3,
  max(case code3 when '03' then b.idname else '' end) idname3
from tb a,item b
where a.code3 = b.idcode
group by a.code1,a.code2drop table tb,item/*
code1      code2      f1         idname1              f2         idname2              f3         idname3
---------- ---------- ---------- -------------------- ---------- -------------------- ---------- --------------------
01151      A19        01         你好                   02         谢谢                   03         学习
01C14      K93        01         你好                   02         谢谢                   03         学习
01M75      C07        01         你好                   02         谢谢                   03         学习(3 行受影响)
*/

Answer 5

create table tb(code1 varchar(10),code2 varchar(10),code3 varchar(10))
insert tb select '01151' , 'A19' , '01' 
insert tb select '01151' , 'A19' , '02' 
insert tb select '01151' , 'A19' , '03' 
insert tb select '01M75' , 'C07' , '01' 
insert tb select '01M75' , 'C07' , '02' 
insert tb select '01M75' , 'C07' , '03' 
insert tb select '01C14' , 'K93' , '01' 
insert tb select '01C14' , 'K93' , '02' 
insert tb select '01C14' , 'K93' , '03'
create table item(idcode varchar(10),idname varchar(20))
insert item select '01','你好'
insert item select '02','谢谢'
insert item select '03','学习'
go
--静态SQL
select a.code1,a.code2,
  max(case a.code3 when '01' then a.code3 else '' end) f1,
  max(case a.code3 when '01' then b.idname else '' end) idname1,
  max(case a.code3 when '02' then a.code3 else '' end) f2,
  max(case a.code3 when '02' then b.idname else '' end) idname2,
  max(case a.code3 when '03' then a.code3 else '' end) f3,
  max(case a.code3 when '03' then b.idname else '' end) idname3
from tb a,item b
where a.code3 = b.idcode
group by a.code1,a.code2
/*
code1      code2      f1         idname1              f2         idname2              f3         idname3
---------- ---------- ---------- -------------------- ---------- -------------------- ---------- --------------------
01151      A19        01         你好                   02         谢谢                   03         学习
01C14      K93        01         你好                   02         谢谢                   03         学习
01M75      C07        01         你好                   02         谢谢                   03         学习
(3 行受影响)
*/--动态SQL
declare @sql varchar(8000)
set @sql = 'select a.code1,a.code2'
select @sql = @sql + ' , max(case code3 when ''' + a.code3 + ''' then code3 else '' '' end) [f' + cast(cast(code3 as int) as varchar)+ ']'
                   + ' , max(case code3 when ''' + a.code3 + ''' then b.idname else '' '' end) [idname' + cast(cast(code3 as int) as varchar)+ ']'
from (select distinct code3 from tb) as a
set @sql = @sql + ' from tb a,item b where a.code3 = b.idcode group by a.code1,a.code2'
exec(@sql) 
/*
code1      code2      f1         idname1              f2         idname2              f3         idname3
---------- ---------- ---------- -------------------- ---------- -------------------- ---------- --------------------
01151      A19        01         你好                   02         谢谢                   03         学习
01C14      K93        01         你好                   02         谢谢                   03         学习
01M75      C07        01         你好                   02         谢谢                   03         学习(3 行受影响)
*/drop table tb,item