隨筆檔案(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?? 學號,姓名