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