從部落格園中看到一篇文章，介紹大軟體公司面試時常常會出的兩道SQL題（見附錄）。
我覺得受益很多，在此之前，我一直覺得，SQL2008似乎提供了這方面的支援，但更低的版本，包括2005，非遊标做不出來（水準夠菜）。總結心得如下：
1、 強大的group by
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
在這裡，group by與sum + case結合，可以将表1中的記錄（行）變成表2的字段（列）。Sum裡面如果沒有case，那麼出來的值，隻能是全部科目的總和，用了case以後，就是某科的成績；然後這裡用了好幾個sum，每個科目一個sum，于是表1中本來某人某科占一條記錄的“行”就變成了表2裡某人一條記錄，每科做一個字段了。
這種心思巧妙和對文法的熟練運用讓人擊節贊歎。
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語句。當然現實中，如果#student表很大，這種做法并不妥，應該都有一個專門的科目類别表的。
3、 在臨時庫中提煉出字段名。臨時表是真實存在的表，儲存在[tempdb]中，可以利用object_id('tempdb.dbo.表名')的方式獲得字段資訊。
============================================
附錄：
Kiler已經把他翻譯成中文版的了，收益一大片僅僅是CET-4的人。不管你是用NHibernate也好，還是用LINQ toSQL也好，用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新手們重構到答案。用MVP李建忠老師最愛說的話就是------我不建議一上來就套用模式，而應該從重構到模式。
首先大家會想到分兩組
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）,
)
2
isnull(sum(
case
stdsubject when
'
國文
'
then Result end),
)
3
isnull(sum(
case
stdsubject when
'
···
'
then Result end),
)
4
isnull(sum(
case
stdsubject when
'
···
'
then Result end),
)
5
isnull(sum(
case
stdsubject when
'
···
'
then Result end),
)
是以得出：
代碼
1 select stdname,
2
isnull(sum(
case
stdsubject when
'
化學
'
then Result end),
) [化學],
3
isnull(sum(
case
stdsubject when
'
數學
'
then Result end),
) [數學],
4
isnull(sum(
case
stdsubject when
'
實體
'
then Result end),
) [實體],
5
isnull(sum(
case
stdsubject when
'
國文
'
then Result end),
) [國文]
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
壓軸題第二問：把表二轉化為表一
表一：
表二：
資料庫代碼如下：
代碼
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 (
'
張三
'
,
,
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
=
'
數學
'
, 數學
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
=
'
數學
'
, 數學
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
=
'
張三
'
重構到：
代碼
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,也是慢慢重構--調試--重構-······
----------------------------------------------------------------------------------------------------------
對于行轉列，或者列轉行，2005及以上版本，有兩個運算符：Pivot 和 UnPivot。
這兩個運算符，不甚了了，幫助也貌似語焉不詳。我試了一下，它們都隻對數值型字段才有效。
use [test]
go
declare @isTempTable int = OBJECT_ID(N'tempdb.dbo.#t');
IF (@isTempTable IS NOT NULL)
BEGIN
DROP TABLE #t;
END
GO
select * into #t from score;
ALTER table #t add number int;
UPDATE #t set number=case subject when '國文' then 1 WHEN '數學' then 2 else 3 end;
select * from #t;
select [name],[1] as 國文,[2] as 數學,[3] as 實體
from (select [name],result,number from #t) as t
pivot(sum(result) for number in([1],[2],[3])) as p 得到的結果是
name subject result number
張三 國文 80 1
張三 數學 90 2
張三 實體 85 3
李四 國文 85 1
李四 數學 92 2
李四 實體 82 3
name 國文 數學 實體
李四 85 92 82
張三 80 90 85
但如果将
pivot(sum(result) for number in([1],[2],[3])) as p 換成 pivot(sum(result) for result in([1],[2],[3])) as p
select [name],[1] as 國文,[2] as 數學,[3] as 實體
from (select [name],result from #t) as t
pivot(sum(result) for result in([1],[2],[3])) as p 結果将是這樣
name 國文 數學 實體
李四 NULL NULL NULL
張三 NULL NULL NULL
================================================================
腳本
--CREATE TABLE #student (stdname nvarchar(10),stdsubject nvarchar(10),result int)
--INSERT INTO #student VALUES ('張三','國文',80)
--INSERT INTO #student values ('張三','數學',90)
--INSERT INTO #student VALUES ('張三','實體',85)
--INSERT INTO #student VALUES ('李四','國文',85)
--INSERT INTO #student values ('李四','數學',92)
--INSERT INTO #student VALUES ('李四','實體',82)
--INSERT INTO #student VALUES ('李四','化學',82)
--INSERT INTO #student VALUES ('李四','化學',82)
SELECT * FROM #student;
--方法一
select stdname
,(select result from #student where stdname=s.stdname and stdsubject='國文') as '國文'
,(select result from #student where stdname=s.stdname and stdsubject='數學') as '數學'
,(select result from #student where stdname=s.stdname and stdsubject='實體') as '實體'
from #student s
group by stdname;
--方法二
select stdname
,isnull(sum(case when stdsubject='國文' then result else 0 end),0) as '國文'
,isnull(sum(case when stdsubject='數學' then result else 0 end),0) as '數學'
,isnull(sum(case when stdsubject='實體' then result else 0 end),0) as '實體'
into #student2
from #student s
group by stdname;
select * from #student2;
select stdname,stdsubject='國文',result=國文 from #student2
union all
select stdname,stdsubject='數學',result=數學 from #student2
union all
select stdname,stdsubject='實體',result=實體 from #student2
order by stdname;