各大軟體公司.NET工程師筆試壓軸題之關於表的行列轉換__.net

【各大軟體公司.NET工程師筆試壓軸題】------不得不看

      縱覽各大社區、論壇，各大ORM架構火得不行了，如NHibernate、LINQ to SQL、ADO.NET Entity framework等，還有最近市場上出版的一本叫《領域驅動設計與模式實戰》，裡面也凸顯了不少NHibernate在領域驅動設計中的作用與地位，也算是第一本與NHibernate相關的書籍吧。不過就NHibernate而言還是沒有官方文檔介紹得詳細呵呵，園子裡Kiler已經把他翻譯成中文版的了，收益一大片僅僅是CET-4的人。不管你是用NHibernate也好，還是用LINQ to SQL也好，用profiler一跟蹤，執行的都是SQL語句，所以所SQL是根。特別是對於那些以資料為中心的應用系統，在資料庫中實現複雜的預存程序，複雜的報表查詢，還是直接SQL來得痛快。當然對於那些在基於.NET的中介層應用中，它們實現物件導向的業務模型和商業邏輯的應用，NHibernate是最有用的。不管怎樣，NHibernate一定可以協助你消除或者封裝那些針對特定廠商的SQL代碼，並且幫你把結果集從表格式的表示形式轉換到一系列的對象去（官方文檔）。

      有點跑題了，不再囉嗦----直接晾出壓軸題。

壓軸題第一問

1.把表一轉換為表二

表一：                                                                     

表二：

資料庫代碼如下：

代碼
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->  1  DROP table #student
 2  CREATE TABLE #student (stdname nvarchar( 10 ),stdsubject nvarchar( 10 ),result  int )
 3  INSERT INTO #student VALUES ( ' 張三 ' , ' 語文 ' , 80 )
 4  INSERT INTO #student values ( ' 張三 ' , ' 數學 ' , 90 )
 5  INSERT INTO #student VALUES ( ' 張三 ' , ' 物理 ' , 85 )
 6  INSERT INTO #student VALUES ( ' 李四 ' , ' 語文 ' , 85 )
 7  INSERT INTO #student values ( ' 李四 ' , ' 數學 ' , 92 )
 8  INSERT INTO #student VALUES ( ' 李四 ' , ' 物理 ' , 82 )
 9  INSERT INTO #student VALUES ( ' 李四 ' , ' 化學 ' , 82 )
10  INSERT INTO #student VALUES ( ' 李四 ' , ' 化學 ' , 82 )
11  SELECT  *  FROM #student

 

 

 可能很多老手們，一看到這題目就有了答案。當然，貼出答案來不是我的目的，我要帶著SQL新手們重構到答案。用李建忠老師最愛說的話就是------我不建議一上來就套用模式，而應該從重構到模式。

首先大家會想到分兩組

1 select stdname，····，from #student group by stdname

 

 

然後······中間該寫什麼呢。

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--> 代碼
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1  case  stdsubject when  ' 化學 '  then Result end
2  case  stdsubject when  ' 語文 '  then Result end
3  case  stdsubject when  ' ··· '  then Result end
4  case  stdsubject when  ' ··· '  then Result end
5  case  stdsubject when  ' ··· '  then Result end

 

表二裡面得0是哪裡來的呢。

代碼
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1  isnull(sum（ case  stdsubject when  ' 化學 '  then Result end）, 0 )
2  isnull(sum( case  stdsubject when  ' 語文 '  then Result end), 0 )
3  isnull(sum( case  stdsubject when  ' ··· '  then Result end), 0 )
4  isnull(sum( case  stdsubject when  ' ··· '  then Result end), 0 )
5  isnull(sum( case  stdsubject when  ' ··· '  then Result end), 0 )

 

 

所以得出：

代碼
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1  select stdname,
2  isnull(sum( case  stdsubject when  ' 化學 '  then Result end), 0 ) [化學],
3  isnull(sum( case  stdsubject when  ' 數學 '  then Result end), 0 ) [數學],
4  isnull(sum( case  stdsubject when  ' 物理 '  then Result end), 0 ) [物理],
5  isnull(sum( case  stdsubject when  ' 語文 '  then Result end), 0 ) [語文] 
6  from #student 
7  group by stdname

 

 

然後得出答案：

代碼
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1  declare @sql varchar( 4000 ) 
2  set  @sql  =   ' select stdname '  
3  select @sql  =  @sql  +   ' ,isnull(sum(case stdsubject when  ''' + stdsubject + '''  then Result end),0) [ ' + stdsubject + ' ] '  
4  from (select distinct stdsubject from #student)  as  a 
5  select @sql  =  @sql + '  from #student group by stdname '  
6  print @sql
7  exec(@sql)
8 

 

 

 

 

壓軸題第二問：把表二轉化為表一

表一：

表二：

資料庫代碼如下：

代碼
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--> 1  DROP table #student2
2  CREATE TABLE #student2 (stdname nvarchar( 10 ),化學  int ,數學  int ,物理  int  ,語文  int  )
3  INSERT INTO #student2 VALUES ( ' 李四 ' , 164 , 92 , 82 , 85 )
4  INSERT INTO #student2 VALUES ( ' 張三 ' , 0 , 90 , 85 , 80 )
5  SELECT  *  FROM #student2 

 

 

看到這題，直接想到：

 

代碼
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->  1  SELECT ' 李四 ' as  stdname,stdname = ' 化學 ' , 化學  as  result from #student2  where  stdname = ' 李四 '
 2  union all
 3  SELECT ' 李四 ' as  stdname,stdname = ' 數學 ' , 數學  as  result from #student2  where  stdname = ' 李四 '
 4  union all
 5  SELECT ' 李四 ' as  stdname,stdname = ' 物理 ' , 物理  as