資料庫行轉列的sql語句(zt)

問題描述

假設有張學生成績表(CJ)如下

Name Subject Result

張三 國文 80

張三 數學 90

張三 實體 85

李四 國文 85

李四 數學 92

李四 實體 82

現在 想寫 sql 語句     查詢後結果 為   

姓名 國文 數學 實體

張三 80 90 85

李四 85 92 82       該怎麼實作 ？

研究意義

        這是個并不複雜的問題，但卻是資料庫中行轉列的一個典型例子，隻要把這個抽象出來的具有普遍意義的問題研究透徹，其他類似的複雜問題迎刃而解。

問題分析

       首先介紹下行轉列的概念，也許書上并沒有這個概念，行轉列說的是這樣一類問題：有時候為了資料庫表的設計滿足使用者的動态要求（比如添加字段），我們采用定義字段名表，然後定義一個字段值的表，這樣就達到了用靜态來表達動态，換句話說就是把資料庫表中本來應該是橫向的延伸轉化為縱向的延伸，再換句話說就是把資料庫表中本來應該是字段的增加轉化為記錄條數的增加。然而，在這樣設計下，固然靈活，确帶來了統計分析的麻煩，因為統計分析時，應該是以直覺的形式進行表現。換言之，統計分析時，我們又應該顯示為字段更多的那種。如果同時做到了資料存儲時列的增加轉化為行的增加，資料提取時又可得到列增加了的資料，資料庫表的這種設計就對使用者透明了。

        本文前面提出的這個問題就是一個典型的在資料提取時要把以行增加形式的資料轉化為以列增加形式的資料。為什麼這樣說呢？我們注意subject字段，subject裡的内容在資料庫存儲時是以不同資料行的形式，換言之，是以行增加的形式，而輸出時，這裡面的内容我們要變成字段名了。

        衡量這個問題解決好壞我們有幾個标準：1.當資料正好就是上面這個樣子時，解決辦法能否得到正确的解；2.如果增加科目了科目的種類，解決方法是否仍然能行得通；3.如果有些人的某們課程的成績還沒有下來，換言之，資料庫中不是每個人每門課的成績都可以找到，資料庫缺少某個人某門課的成績的記錄。在這種情況下程式還能否得到合理的結果。

試驗環境

       本試驗使用MS SQL Server 2005環境測試。

試驗過程

       1.建立資料表，錄入資料

        CREATE TABLE [dbo].[CJ](

[name] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,

[subject] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,

[result] [int] NULL,

CONSTRAINT [PK_CJ] PRIMARY KEY CLUSTERED

(

[name] ASC,

[subject] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

     通過可視化界面或者用insert語句錄入資料

     2.第一個最直接，最簡單的做法

         select distinct c.[name] as 姓名,

(select result from CJ where [name] = c.[name] and subject = '國文' )as 國文,

(select result from CJ where [name] = c.[name] and subject = '數學' )as 數學,

(select result from CJ where [name] = c.[name] and subject = '實體' )as 實體

from CJ c

          主要思想就是把任務分成兩步，第一步：把第一列生成出來。第二步：根據第一列每行的姓名取值，查詢該同學的各科成績join到第一步生成的隻有一清單。distinct不能省略。

          該方法能夠完成該任務，但隻能滿足前文所述的評價标準1和标準3。當科目增多或者實際科目沒有這麼多時統計的結果就不那麼完美了。換言之，這種方法是靜态的，将科目在sql語句裡寫死了。另外中間的幾個sql語句查詢效率似乎并不那麼高，還需要掃描整個表，實際上應該隻需要在一個學生對應的幾條記錄裡找就可以了。

       3.較好的辦法

      先不管标準2，想想能不能解決那個掃描的效率問題。于是得到了下面的辦法。

      select   [name] as 姓名,

sum(case when subject='國文' then result end) as 國文,

sum(case when subject='數學' then result end) as 數學,

sum(case when subject='實體' then result end) as 實體

from CJ group by [name]

      該辦法大緻思想類似前一種。最大的改進是用了group by，由于用了group by後字段名除了group by的那個其他不能直接用，加了個集函數，實際上這個Sum隻會加一項，因為這個表的主鍵是name + subject。用了group by就會解決掃描的效率問題，因為sum是計算的每個分組之類的。本方法的技巧之處在于case when的使用。

       這個辦法還是不能滿足标準2。

      4.較完美的辦法

     現在就是怎麼解決subject“由死到活”的問題。想到了一種辦法如下：

    declare @s nvarchar(1000)

select @s = 'select [name] as 姓名'

select @s = @s + ',sum(case when subject=''' + cast(subject as varchar) + ''' then result end) as ' + subject from CJ group by subject

select @s = @s + ' from CJ group by [name]'

exec(@s)

     其實思想是基于前面那種辦法的，關鍵的地方就是通過動态生成sql語句，然後執行之。

    在@s的第一次累加中的代碼中一句from CJ group by subject很是有技巧性，可見簡單的select * from table t where .. 也是這麼變化無窮，不得不佩服sql或者說關系型資料庫的智慧。

本人收獲

    a.認真的分析一個簡單的問題的來龍去脈是很有意義的事情，浮躁的學風會讓你花費大量的時間結果一無所獲。

    b.解決一個問題要有清晰的思路，在一時不知道完美的答案時，可試圖一步一步優化，向完美的方向靠近。

    c.要善于分析問題的症結所在，即抓住問題的本質。

寫到最後

    這個問題暫時就說到這裡，之是以把文章寫出來是基于兩個目的，首先，作為學習心得，不敢獨享，希望更多的人能從中得到啟發。其次，簡單的問題也包含很多高深的知識，希望更多的高手能加入探讨，分析本文的不當之處，并給出更好的辦法，或者提供更多的類似的例子，本文希望起到抛磚引玉的作用。