從【各大軟件公司筆試壓軸題】學習SQL語句 收藏 此文于2009-12-31被推薦到CSDN首頁
此文于2010-01-04被推薦到CSDN首頁
如何被推薦？
從博客園中看到一篇文章，介紹大軟件公司面試時常常會出的兩道SQL題（見附錄）。
我覺得受益很多，在此之前，我一直覺得，SQL2008似乎提供了這方面的支持，但更低的版本，包括2005，非游標做不出來（水平夠菜）?？偨Y(jié)心得如下：
1、 強大的group by
1 select stdname,
2 isnull(sum( case stdsubject when ' 化學 ' then Result end), 0 ) [化學],
3 isnull(sum( case stdsubject when ' 數(shù)學 ' then Result end), 0 ) [數(shù)學],
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
在這里，group by與sum + case結(jié)合，可以將表1中的記錄（行）變成表2的字段（列）。Sum里面如果沒有case，那么出來的值，只能是全部科目的總和，用了case以后，就是某科的成績；然后這里用了好幾個sum，每個科目一個sum，于是表1中本來某人某科占一條記錄的“行”就變成了表2里某人一條記錄，每科做一個字段了。
這種心思巧妙和對語法的熟練運用讓人擊節(jié)贊嘆。
2、 利用select from (select from)的模式生成SQL語句
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)
為了自動寫上所有的科目，這里先將科目信息提煉出來：
4 from (select distinct stdsubject from #student) as a
利用之拼接生成SQL語句。當然現(xiàn)實中，如果#student表很大，這種做法并不妥，應該都有一個專門的科目類別表的。
3、 在臨時庫中提煉出字段名。臨時表是真實存在的表，保存在[tempdb]中，可以利用object_id('tempdb.dbo.表名')的方式獲得字段信息。
============================================
附錄：
縱覽各大社區(qū)、論壇，各大 ORM框架火得不行了，如NHibernate、LINQ to SQL、ADO.NET Entity framework等，還有最近市場上出版的一本叫《領域驅(qū)動設計與模式實戰(zhàn)》，里面也凸顯了不少NHibernate在領域驅(qū)動設計中的作用與地位，也算是第一本與NHibernate相關的書籍吧！不過就NHibernate而言還是沒有官方文檔介紹得詳細呵呵，園子里Kiler 已經(jīng)把他翻譯成中文版的了，收益一大片僅僅是CET-4的人。不管你是用NHibernate也好，還是用LINQ to SQL也好，用profiler一跟蹤，執(zhí)行的都是SQL語句，所以所SQL是根。特別是對于那些以數(shù)據(jù)為中心的應用系統(tǒng)，在數(shù)據(jù)庫中實現(xiàn)復雜的存儲過程，復雜的報表查詢，還是直接SQL來得痛快。當然 對于那些在基于.NET的中間層應用中，它們實現(xiàn)面向?qū)ο蟮臉I(yè)務模型和商業(yè)邏輯的應用，NHibernate是最有用的。不管怎樣，NHibernate一定可以幫助你消除或者包裝那些針對特定廠商的SQL代碼，并且?guī)湍惆呀Y(jié)果集從表格式的表示形式轉(zhuǎn)換到一系列的對象去（官方文檔）。
有點跑題了，不再啰嗦----直接晾出壓軸題。
壓軸題第一問
1.把表一轉(zhuǎn)換為表二
表一：
表二：
數(shù)據(jù)庫代碼如下：
代碼
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 ( ' 張三 ' , ' 數(shù)學 ' , 90 )
5 INSERT INTO #student VALUES ( ' 張三 ' , ' 物理 ' , 85 )
6 INSERT INTO #student VALUES ( ' 李四 ' , ' 語文 ' , 85 )
7 INSERT INTO #student values ( ' 李四 ' , ' 數(shù)學 ' , 92 )
8 INSERT INTO #student VALUES ( ' 李四 ' , ' 物理 ' , 82 )
9 INSERT INTO #student VALUES ( ' 李四 ' , ' 化學 ' , 82 )
10 INSERT INTO #student VALUES ( ' 李四 ' , ' 化學 ' , 82 )
11 SELECT * FROM #student
可能很多老手們，一看到這題目就有了答案。當然，貼出答案來不是我的目的，我要帶著SQL新手們重構(gòu)到答案。用MVP李建忠老師最愛說的話就是------我不建議一上來就套用模式，而應該從重構(gòu)到模式。
首先大家會想到分兩組
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 ' 數(shù)學 ' then Result end), 0 ) [數(shù)學],
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)
8
壓軸題第二問：把表二轉(zhuǎn)化為表一
表一：
表二：
數(shù)據(jù)庫代碼如下：
代碼
1 DROP table #student2
2 CREATE TABLE #student2 (stdname nvarchar( 10 ),化學 int ,數(shù)學 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 SELECT ' 李四 ' as stdname,stdname = ' 化學 ' , 化學 as result from #student2 where stdname = ' 李四 '
2 union all
3 SELECT ' 李四 ' as stdname,stdname = ' 數(shù)學 ' , 數(shù)學 as result from #student2 where stdname = ' 李四 '
4 union all
5 SELECT ' 李四 ' as stdname,stdname = ' 物理 ' , 物理 as result from #student2 where stdname = ' 李四 '
6 union all
7 SELECT ' 李四 ' as stdname,stdname = ' 語文 ' , 語文 as result from #student2 where stdname = ' 李四 '
8 union all
9 SELECT ' 張三 ' as stdname,stdname = ' 化學 ' , 化學 as result from #student2 where stdname = ' 張三 '
10 union all
11 SELECT ' 張三 ' as stdname,stdname = ' 數(shù)學 ' , 數(shù)學 as result from #student2 where stdname = ' 張三 '
12 union all
13 SELECT ' 張三 ' as stdname,stdname = ' 物理 ' , 物理 as result from #student2 where stdname = ' 張三 '
14 union all
15 SELECT ' 張三 ' as stdname,stdname = ' 語文 ' , 語文 as result from #student2 where stdname = ' 張三 '
重構(gòu)到：
代碼
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 = '' 數(shù)學 '' , 數(shù)學 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)
如果要求不能出現(xiàn) 化學 數(shù)學 物理 語文 這樣的關鍵字，那么可以這樣寫：
代碼
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)
這種題目，在各種筆試中出現(xiàn)的概率還是非常大的，大家不用死記。以前有的朋友看著復雜的報表查詢，幾百行SQL,望而生畏，然后說："這是哪個SQL超人寫的?。?其實，誰一上來不可能寫出那么長的SQL,也是慢慢重構(gòu)--調(diào)試--重構(gòu)-······
發(fā)表于 @ 2009年12月29日 11:32:00 | 評論( 60 ) | 編輯| 舉報| 收藏
舊一篇:網(wǎng)站的最大并發(fā)連接數(shù)
