?在寫系統(tǒng)尤其是在寫課程系統(tǒng)是經(jīng)常會(huì)遇到類似如下縱-橫轉(zhuǎn)換，在使用group by ... with cube/rollup + grouping() 來(lái)實(shí)現(xiàn)是非常容易做到的。
?
1. 用于測(cè)試的數(shù)據(jù)如下：
declare
?
@tab
?
table
(Class?
varchar
(
20
),Student?
varchar
(
20
),Course?
varchar
(
50
),Grades?
decimal
(
7
,
2
));
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' A班 ' , ' 張三 ' , ' 語(yǔ)文 ' , 60 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' A班 ' , ' 張三 ' , ' 數(shù)學(xué) ' , 70 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' A班 ' , ' 張三 ' , ' 英語(yǔ) ' , 80 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' A班 ' , ' 李四 ' , ' 語(yǔ)文 ' , 30 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' A班 ' , ' 李四 ' , ' 數(shù)學(xué) ' , 40 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' A班 ' , ' 李四 ' , ' 英語(yǔ) ' , 50 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' B班 ' , ' 王五 ' , ' 語(yǔ)文 ' , 65 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' B班 ' , ' 王五 ' , ' 數(shù)學(xué) ' , 75 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' B班 ' , ' 王五 ' , ' 英語(yǔ) ' , 85 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' B班 ' , ' 趙六 ' , ' 語(yǔ)文 ' , 35 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' B班 ' , ' 趙六 ' , ' 數(shù)學(xué) ' , 45 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' B班 ' , ' 趙六 ' , ' 英語(yǔ) ' , 55 );
select ? * ? from ? @tab
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' A班 ' , ' 張三 ' , ' 語(yǔ)文 ' , 60 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' A班 ' , ' 張三 ' , ' 數(shù)學(xué) ' , 70 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' A班 ' , ' 張三 ' , ' 英語(yǔ) ' , 80 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' A班 ' , ' 李四 ' , ' 語(yǔ)文 ' , 30 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' A班 ' , ' 李四 ' , ' 數(shù)學(xué) ' , 40 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' A班 ' , ' 李四 ' , ' 英語(yǔ) ' , 50 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' B班 ' , ' 王五 ' , ' 語(yǔ)文 ' , 65 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' B班 ' , ' 王五 ' , ' 數(shù)學(xué) ' , 75 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' B班 ' , ' 王五 ' , ' 英語(yǔ) ' , 85 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' B班 ' , ' 趙六 ' , ' 語(yǔ)文 ' , 35 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' B班 ' , ' 趙六 ' , ' 數(shù)學(xué) ' , 45 );
insert ? into ? @tab (Class,Student,Course,Grades)? values ( ' B班 ' , ' 趙六 ' , ' 英語(yǔ) ' , 55 );
select ? * ? from ? @tab
?
?
2. 實(shí)現(xiàn)此效果的SQL語(yǔ)句如下：
select
?
????( case ? when ? Grouping (Class) = 1 ? then ? ' 總平均 ' ? when ? Grouping (Student) = 1 ? then ? '' ? else ?Class? end ?)? as ?Class
????,( case ? when ? Grouping (Class) = 1 ? then ? '' ? when ? Grouping (Student) = 1 ? then ? ' 平均 ' ? else ?Student? end )? as ?Student
????, cast ( avg (語(yǔ)文)? as ? decimal ( 7 , 2 ))? as ?語(yǔ)文
????, cast ( avg (數(shù)學(xué))? as ? decimal ( 7 , 2 ))? as ?數(shù)學(xué)
????, cast ( avg (英語(yǔ))? as ? decimal ( 7 , 2 ))? as ?英語(yǔ)?
????, cast ( avg (總分)? as ? decimal ( 7 , 2 ))? as ?總分
from ?(
???? select ?Class,Student
????,( select ? isnull ( sum (Grades), 0 )? from ? @tab ? where ?Class = t.Class? and ?Student = t.Student? and ?Course = ' 語(yǔ)文 ' )? as ? ' 語(yǔ)文 '
????,( select ? isnull ( sum (Grades), 0 )? from ? @tab ? where ?Class = t.Class? and ?Student = t.Student? and ?Course = ' 數(shù)學(xué) ' )? as ? ' 數(shù)學(xué) '
????,( select ? isnull ( sum (Grades), 0 )? from ? @tab ? where ?Class = t.Class? and ?Student = t.Student? and ?Course = ' 英語(yǔ) ' )? as ? ' 英語(yǔ) '
????,( select ? isnull ( sum (Grades), 0 )? from ? @tab ? where ?Class = t.Class? and ?Student = t.Student)? as ? ' 總分 '
???? from ? @tab ? as ?t
???? group ? by ?Class,Student
????)? as ?tempTab
group ? by ?Class,Student,語(yǔ)文,數(shù)學(xué),英語(yǔ),總分? with ?rollup
having ? Grouping (語(yǔ)文) = 1 ?
???? and ? Grouping (數(shù)學(xué)) = 1
???? and ? Grouping (英語(yǔ)) = 1
????( case ? when ? Grouping (Class) = 1 ? then ? ' 總平均 ' ? when ? Grouping (Student) = 1 ? then ? '' ? else ?Class? end ?)? as ?Class
????,( case ? when ? Grouping (Class) = 1 ? then ? '' ? when ? Grouping (Student) = 1 ? then ? ' 平均 ' ? else ?Student? end )? as ?Student
????, cast ( avg (語(yǔ)文)? as ? decimal ( 7 , 2 ))? as ?語(yǔ)文
????, cast ( avg (數(shù)學(xué))? as ? decimal ( 7 , 2 ))? as ?數(shù)學(xué)
????, cast ( avg (英語(yǔ))? as ? decimal ( 7 , 2 ))? as ?英語(yǔ)?
????, cast ( avg (總分)? as ? decimal ( 7 , 2 ))? as ?總分
from ?(
???? select ?Class,Student
????,( select ? isnull ( sum (Grades), 0 )? from ? @tab ? where ?Class = t.Class? and ?Student = t.Student? and ?Course = ' 語(yǔ)文 ' )? as ? ' 語(yǔ)文 '
????,( select ? isnull ( sum (Grades), 0 )? from ? @tab ? where ?Class = t.Class? and ?Student = t.Student? and ?Course = ' 數(shù)學(xué) ' )? as ? ' 數(shù)學(xué) '
????,( select ? isnull ( sum (Grades), 0 )? from ? @tab ? where ?Class = t.Class? and ?Student = t.Student? and ?Course = ' 英語(yǔ) ' )? as ? ' 英語(yǔ) '
????,( select ? isnull ( sum (Grades), 0 )? from ? @tab ? where ?Class = t.Class? and ?Student = t.Student)? as ? ' 總分 '
???? from ? @tab ? as ?t
???? group ? by ?Class,Student
????)? as ?tempTab
group ? by ?Class,Student,語(yǔ)文,數(shù)學(xué),英語(yǔ),總分? with ?rollup
having ? Grouping (語(yǔ)文) = 1 ?
???? and ? Grouping (數(shù)學(xué)) = 1
???? and ? Grouping (英語(yǔ)) = 1
?
?
這里沒(méi)有考慮做到通用，如果做到通用可能會(huì)比較復(fù)雜，也不知道性能會(huì)怎么樣。
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
