国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看

SQL code

--行列互轉(zhuǎn)/******************************************************************************************************************************************************以學(xué)生成績(jī)?yōu)槔樱容^形象易懂整理人：中國(guó)風(fēng)(Roy)日期:2008.06.06******************************************************************************************************************************************************/--1、行互列--> --> (Roy)生成測(cè)試數(shù)據(jù) if not object_id('Class') is null    drop table ClassGoCreate table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)Insert Classselect N'張三',N'語(yǔ)文',78 union allselect N'張三',N'數(shù)學(xué)',87 union allselect N'張三',N'英語(yǔ)',82 union allselect N'張三',N'物理',90 union allselect N'李四',N'語(yǔ)文',65 union allselect N'李四',N'數(shù)學(xué)',77 union allselect N'李四',N'英語(yǔ)',65 union allselect N'李四',N'物理',85 Go--2000方法:動(dòng)態(tài):declare @s nvarchar(4000)set @s=''Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'from Class group by[Course]exec('select [Student]'+@s+' from Class group by [Student]')生成靜態(tài):select     [Student],    [數(shù)學(xué)]=max(case when [Course]='數(shù)學(xué)' then [Score] else 0 end),    [物理]=max(case when [Course]='物理' then [Score] else 0 end),    [英語(yǔ)]=max(case when [Course]='英語(yǔ)' then [Score] else 0 end),    [語(yǔ)文]=max(case when [Course]='語(yǔ)文' then [Score] else 0 end) from     Class group by [Student]GO動(dòng)態(tài):declare @s nvarchar(4000)Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成靜態(tài):select * from     Class pivot     (max([Score]) for [Course] in([數(shù)學(xué)],[物理],[英語(yǔ)],[語(yǔ)文]))b生成格式:/*Student 數(shù)學(xué)          物理          英語(yǔ)          語(yǔ)文------- ----------- ----------- ----------- -----------李四      77          85          65          65張三      87          90          82          78(2 行受影響)*/------------------------------------------------------------------------------------------go--加上總成績(jī)(學(xué)科平均分)--2000方法:動(dòng)態(tài):declare @s nvarchar(4000)set @s=''Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'from Class group by[Course]exec('select [Student]'+@s+',[總成績(jī)]=sum([Score])  from Class group by [Student]')--加多一列(學(xué)科平均分用avg([Score]))生成動(dòng)態(tài):select     [Student],    [數(shù)學(xué)]=max(case when [Course]='數(shù)學(xué)' then [Score] else 0 end),    [物理]=max(case when [Course]='物理' then [Score] else 0 end),    [英語(yǔ)]=max(case when [Course]='英語(yǔ)' then [Score] else 0 end),    [語(yǔ)文]=max(case when [Course]='語(yǔ)文' then [Score] else 0 end),    [總成績(jī)]=sum([Score]) --加多一列(學(xué)科平均分用avg([Score]))from     Class group by [Student]go--2005方法:動(dòng)態(tài):declare @s nvarchar(4000)Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一個(gè)逗號(hào)exec('select [Student],'+@s+',[總成績(jī)] from (select *,[總成績(jī)]=sum([Score])over(partition by [Student]) from Class) a pivot (max([Score]) for [Course] in('+@s+'))b ')生成靜態(tài):select     [Student],[數(shù)學(xué)],[物理],[英語(yǔ)],[語(yǔ)文],[總成績(jī)] from     (select *,[總成績(jī)]=sum([Score])over(partition by [Student]) from Class) a --平均分時(shí)用avg([Score])pivot     (max([Score]) for [Course] in([數(shù)學(xué)],[物理],[英語(yǔ)],[語(yǔ)文]))b 生成格式:/*Student 數(shù)學(xué)          物理          英語(yǔ)          語(yǔ)文          總成績(jī)------- ----------- ----------- ----------- ----------- -----------李四      77          85          65          65          292張三      87          90          82          78          337(2 行受影響)*/go--2、列轉(zhuǎn)行--> --> (Roy)生成測(cè)試數(shù)據(jù) if not object_id('Class') is null    drop table ClassGoCreate table Class([Student] nvarchar(2),[數(shù)學(xué)] int,[物理] int,[英語(yǔ)] int,[語(yǔ)文] int)Insert Classselect N'李四',77,85,65,65 union allselect N'張三',87,90,82,78Go--2000:動(dòng)態(tài):declare @s nvarchar(4000)select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一個(gè)union all+',[Score]='+quotename(Name)+' from Class'from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不轉(zhuǎn)換的列order by Colidexec('select * from ('+@s+')t order by [Student],[Course]')--增加一個(gè)排序生成靜態(tài):select * from (select [Student],[Course]='數(shù)學(xué)',[Score]=[數(shù)學(xué)] from Class union all select [Student],[Course]='物理',[Score]=[物理] from Class union all select [Student],[Course]='英語(yǔ)',[Score]=[英語(yǔ)] from Class union all select [Student],[Course]='語(yǔ)文',[Score]=[語(yǔ)文] from Class)t order by [Student],[Course]go--2005:動(dòng)態(tài):declare @s nvarchar(4000)select @s=isnull(@s+',','')+quotename(Name)from syscolumns where ID=object_id('Class') and Name not in('Student') order by Colidexec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')goselect     Student,[Course],[Score] from     Class unpivot     ([Score] for [Course] in([數(shù)學(xué)],[物理],[英語(yǔ)],[語(yǔ)文]))b生成格式:/*Student Course Score------- ------- -----------李四      數(shù)學(xué)      77李四      物理      85李四      英語(yǔ)      65李四      語(yǔ)文      65張三      數(shù)學(xué)      87張三      物理      90張三      英語(yǔ)      82張三      語(yǔ)文      78(8 行受影響)*/