當耐特

  縱覽各大社區、論壇，各大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.把表一轉換為表二

表一：                                                                     

表二：

數據庫代碼如下：

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

然后······中間該寫什么呢？

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是哪里來的呢？

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) 

所以得出：

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 

然后得出答案：

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) 

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

表一：

表二：

數據庫代碼如下：

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  

看到這題，直接想到：

 重構到：

 1 declare @sql2 varchar(4000)
 2  set @sql2 = '' 
 3 SELECT @sql2=@sql2+ 
 4  'SELECT'''+stdname+'''as stdname,stdname=''化學'', 化學 as result from #student2 where stdname='''+stdname+'''
 5  union all
 6 SELECT'''+stdname+'''as stdname,stdname=''數學'', 數學 as result from #student2 where stdname='''+stdname+'''
 7 union all
 8 SELECT'''+stdname+'''as stdname,stdname=''物理'', 物理 as result from #student2 where stdname='''+stdname+'''
 9 union all
10 SELECT'''+stdname+'''as stdname,stdname=''語文'', 語文 as result from #student2 where stdname='''+stdname+''' union all '
11  from (SELECT stdname FROM #student2) as a
12 SELECT @sql2 = LEFT(@sql2,LEN(@sql2) - 10)
13 PRINT(@sql2)
14 exec(@sql2) 

如果要求不能出現  化學  數學  物理 語文 這樣的關鍵字，那么可以這樣寫：

 1 select [name] into #tmpCloumns
 2 from tempdb.dbo.syscolumns
 3  where id=object_id('tempdb.dbo.#student2')
 4 and [name]<>'stdname'
 5 select *  from #tmpCloumns
 6 
 7 declare @strSql nvarchar(800)
 8 select @strSql=''
 9 select @strSql=@strSql+'union all'+char(10)+char(13)+
10                 'select [stdname],'''+[name]+''' as [科目],['+[name]+']'+char(10)+char(13)+
11                 'from [#student2]'+char(10)+char(13)
12 from #tmpCloumns
13 
14 select @strSql=substring(@strSql,11,len(@strSql))+'order by stdname,[科目]'
15  --print @strSql
16 exec(@strsql)  

 這種題目，在各種筆試中出現的概率還是非常大的，大家不用死記。以前有的朋友看著復雜的報表查詢，幾百行SQL,望而生畏，然后說："這是哪個SQL超人寫的啊！"其實，誰一上來不可能寫出那么長的SQL,也是慢慢重構--調試--重構-······