本文為原創(chuàng)，如需轉(zhuǎn)載，請注明作者和出處，謝謝！
上一篇：SQL Server2005雜談（3）：四個排名函數(shù)（row_number、rank、dense_rank和ntile）的比較
最近做一個項目，遇到一個在分組的情況下，將某一列的字段值（varchar類型）連接起來的問題，類似于sum函數(shù)對int型字段值求和。 如有一個表t_table，結(jié)構(gòu)和數(shù)據(jù)如圖1
圖1
其中要按著xh字段分組，并且將每一組name字段值連接起來。最終結(jié)果希望如圖2所示
圖2
表中的th字段值對于每一個xh值是唯一的，也是有限的，也就是說，對于一個xh值，th的值不會太多，如最多是10個（從1至10）。
以上需求最終想了三種方法來解決這個問題。
一、修改表結(jié)構(gòu)
如果是新的項目，可以考慮修改一下表的結(jié)構(gòu)。如果t_table的結(jié)構(gòu)修改如下：
xh value1 value2
value3 value4 .... .... value10
0001 123456 654321 456789
0002 12abcd 4d2r343 343dfd
0003 abcde3 132323
這種方法將value的值縱向改為橫向，也就是說，按每一個xh值，將value字段的值按逆時針旋轉(zhuǎn)了90度。 但這種方法要有一個前提，就是假設(shè)xh的每一個值所對應(yīng)的value值不會太多，如上面不超過10個，這樣才有可能建立有限個字段。如果按著上面的字段結(jié)構(gòu)，只需要將這些字段加一起就可以了，也不用分組。如下所示：
select xh , (value1 + value2 + value3 + + value10) as value from t_table
但這種方法至少有如下三個缺陷：
1. 需要修改表結(jié)構(gòu)，這對于已經(jīng)進(jìn)行很長時間或是已經(jīng)上線的項目產(chǎn)不適用
2. 對每一個xh字段的value取值數(shù)有限制，如果太多，就得建立很多字段。這樣性能會降低。
3. 這樣做雖然查詢?nèi)菀祝绻枰獙γ恳粋€xh的不同值頻繁修改或加入新的值時，如果把它們都放到一行，容易因為行鎖而降低性能。
二、動態(tài)生成select語句
讓我們先看三條SQL語句：
select xh,value as th1 from t_table where th=1
select xh,value as th2 from t_table where th=2
select xh,value as th3 from t_table where th=3
這三條語句分別使用th字段按著所有th可能的值來查詢t_table，這三條SQL語句所查詢出來的記錄如圖3所示。
圖 3
然后再使用下面的語句按著xh分組：
select xh from t_table group by xh
得到的結(jié)果如圖4所示。
圖4
然后使用left join，以圖4所示的表為最左邊的表，進(jìn)行連接，SQL語句如下：
select a.xh, b.th1, c.th2, d.th3 from
(select xh from t_table group by xh) a
left join
(select xh,value as th1 from t_table where th=1) b on a.xh=b.xh
left join
(select xh,value as th2 from t_table where th=2) c on a.xh=c.xh
left join
(select xh,value as th3 from t_table where th=3) d on a.xh=d.xh
之所以使用left join，是因為按著th查詢后，有的表的某些xh值可以沒有，如圖3中的第三個表，就沒有0003。如果使用內(nèi)連接，0003就無法在記錄集中體現(xiàn)。這面的SQL的查詢結(jié)果如圖5所示。
圖5
然后我們就可以使用如下的語句來連接th1
、th2
和th3
了。
select xh, (th1+th2+th3) as th from myview
myview表示將上面用left join的語句保存成的視圖。
下面可以將這個過程寫成一條SQL語句：
select xh, (th1+th2+th3) as th from
(
select a.xh, (case when b.th1 is null then '' else b.th1 end) as th1,
(case when c.th2 is null then '' else c.th2 end) as th2,
(case when d.th3 is null then '' else d.th3 end) as th3
from
(select xh from t_table group by xh) a
left join
(select xh,value as th1 from t_table where th=1) b on a.xh=b.xh
left join
(select xh,value as th2 from t_table where th=2) c on a.xh=c.xh
left join
(select xh,value as th3 from t_table where th=3) d on a.xh=d.xh
) x
由于null加上任何字符串都為null，因此，使用case語句來將null轉(zhuǎn)換為空串。上面的SQL就會得到圖2所示的查詢結(jié)果。也許有的讀者會問，如果th的可能取值可變呢！如xh為0001的th值四個：1至4。
那上面的SQL不是要再加一個left join嗎？這樣不是很不通用。 要解決這個問題也很容易。可以使用程序（如C#、Java等）自動生成上述的SQL，然后由程序提交給數(shù)據(jù)庫，再執(zhí)行。 當(dāng)然，這需要程序事先知道th值對于當(dāng)前程序最多有幾個值，然后才可以自動生成上述的SQL語句。
這種方法幾乎適合于所有的數(shù)據(jù)庫，不過如果th的取值比較多的話，可能SQL語句會很長，但是如果用程序自動生成的話，就不會管這些了。
三、使用C#實現(xiàn)SQL Server2005的擴(kuò)展聚合函數(shù)（當(dāng)然，也可以用VB.NET）
這一種方法筆者認(rèn)為是最“酷”的方法。因為每一個人都只想寫如下的SQL語句就可以達(dá)到目錄。
select xh, dbo.joinstr(value) from t_table group by xh
其中joinstr是一個聚合函數(shù)，功能是將每一組的某個字符串列的值首尾連接。上面的SQL也可以查詢圖2所示的結(jié)果。但遺憾的是，sql server2005并未提供可以連接字符串的聚合函數(shù)。下面我們就來使用C#來實現(xiàn)一個擴(kuò)展聚合函數(shù)。
首先用VS2008/VS2005建立一個SQL Server項目，如圖6所示。
圖6
點擊“確定”按鈕后，SQL Server項目會要求連接一個數(shù)據(jù)庫，我們可以選擇一個數(shù)據(jù)庫，如圖7所示。
圖7
然后在工程中加入一個聚合類（joinstr.cs），如圖8所示。
圖8
joinstr.cs中的最終代碼如下：
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use custom serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public struct joinstr :IBinarySerialize
{
private System.Text.StringBuilder intermediateResult;
public void Init()
{
// 在此處放置代碼
intermediateResult = new System.Text.StringBuilder();
}
public void Accumulate(SqlString Value)
{
intermediateResult.Append(Value.Value);
}
public void Merge(joinstr Group)
{
intermediateResult.Append(Group.intermediateResult);
}
public SqlString Terminate()
{
return new SqlString(intermediateResult.ToString());
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
由于本例需要聚合字符串，而不是已經(jīng)被序列化的類型，如int等，因此，需要實現(xiàn)IBinarySerialize接口來手動序列化。使用C#實現(xiàn)SQL Server聚合函數(shù)，也會受到字符串最大長度為8000的限制。
在編寫完上述代碼后，可以使用Visual Studio來部署（右向工程，在彈出菜單上選“部署”即可）。也可以使用SQL語句來部署。假設(shè)上面的程序生成的dll為MyAggregate.dll，可以使用下面的SQL語句來部署：
CREATE ASSEMBLY MyAgg FROM 'D:\test\MyAggregate.dll'
CREATE AGGREGATE joinstr (@input nvarchar(200)) RETURNS nvarchar(max)
EXTERNAL NAME MyAgg.joinstr
要注意的是，字符串類型需要用nvarchar，而不能用varchar。
第一條SQL語句是裝載dll，第二條SQL語句是注冊joinstr聚合函數(shù)（每一個C#類就是一個聚合函數(shù)）
在執(zhí)行上面的SQL語句之前，需要將SQL Server2005的clr功能打開。如圖9所示。
圖9
如果想刪除上面建立的聚合函數(shù)，可以使用如下的SQL語句：
drop aggregate joinstr
在刪除聚合函數(shù)后，可以將MyAggregate.dll卸載。
drop assembly MyAgg
OK，現(xiàn)在可以使用joinstr來聚合字符串了。
這種方法雖然顯示很“酷”，但卻要求開發(fā)人員熟悉擴(kuò)展聚合函數(shù)的開發(fā)方法，如果開發(fā)人員使有的不是微軟的開發(fā)工具，如使用Java，恐怕這種方法就只能是空談了（除非開發(fā)小組內(nèi)有人會用微軟的開發(fā)工具）。
當(dāng)然，如果使用其他的數(shù)據(jù)庫，如oracle、mysql，也是可以實現(xiàn)類似擴(kuò)展函數(shù)的功能的，如oracle可以使用java來進(jìn)行擴(kuò)展。但這要求開發(fā)人員具有更高的素質(zhì)。
以上介紹的三種方法僅供參考，至于采用哪種方法，可根據(jù)實際需要和具體情況而定。如果哪位讀者有更好的方法，請跟貼！
下一篇：SQL Server2005雜談（5）：將聚合記錄集逆時針和順時針旋轉(zhuǎn)90度
新浪微博：http://t.sina.com.cn/androidguy 昵稱：李寧_Lining