開發(fā)環(huán)境：SQL Server2008 R2
寫個(gè)綜合視圖，遇到個(gè)情況橡淆，需要對字符串進(jìn)行聚合統(tǒng)計(jì)召噩，簡化如下：
| 任務(wù)號(hào) | 提交人 | 完工數(shù) | 周轉(zhuǎn)車號(hào) |
|---|---|---|---|
| X01 | 張三 | 300 | V001 |
| X01 | 李四 | 200 | V002 |
| X02 | 王五 | 600 | V003 |
| X02 | 馬六 | 400 | V004 |
| X02 | 趙七 | 100 | V005 |
目的是:需要列出統(tǒng)計(jì)任務(wù)的完成信息如下：
| 任務(wù)號(hào) | 提交人 | 完工數(shù) | 周轉(zhuǎn)車號(hào) |
|---|---|---|---|
| X01 | 張三,李四 | 500 | V001,V002 |
| X02 | 王五,馬六，趙七 | 1100 | V003,V004,V005 |
完工數(shù)量可以直接sum 后 group by,但是提交人 和 周轉(zhuǎn)車 字符串字段就很麻煩了师倔。google了下构韵，有以下三種辦法：
- ** 自定義聚合函數(shù) ** 如何在sql server的group by語句中聚合字符串字段
這種方法的思路就是用sql自定義個(gè)function，聚合的時(shí)候調(diào)用溯革。這個(gè)辦法最大的問題就是在函數(shù)中需要把要調(diào)用的表名寫死贞绳，像上面這個(gè)需求，就要定義兩個(gè)函數(shù)致稀，一個(gè)是對提交人的聚合冈闭，一個(gè)是對周轉(zhuǎn)車的聚合，而且這里的識(shí)別id只有一個(gè)抖单，就是任務(wù)id（這個(gè)是簡化需求）萎攒，我的實(shí)際需求是要對任務(wù)ID+工序ID作為子件的，這樣的函數(shù)條件也不好擴(kuò)展矛绘。--所以放棄這個(gè)辦法耍休。 - ** 用stuff和for xml path子查詢 ** SQL SERVER 2005 中使用for xml path('')和stuff合并顯示多行數(shù)據(jù)到一行中
這個(gè)方法也可行，但是問題也和1一樣货矮，要大段大段的寫SQL子查詢羊精，而且無法復(fù)用，多的話實(shí)在受不了囚玫。 - ** 目前找到的以為最好的方法：配合c#自定義聚合函數(shù) ** 源出處：C#實(shí)現(xiàn)SQL Server2005的擴(kuò)展聚合函數(shù)
該方法實(shí)現(xiàn)后喧锦，調(diào)用的SQL就是：
SELECT taskid,SUM(qty),
dbo.StrJoin(workerName,',') as workers, dbo.StrJoin(cartNo,',') as Carts
FROM taskExecs GROUP BY taskid
是不是很簡單？而且以后出現(xiàn)類似的拼接字符串聚合就都直接調(diào)用就好了抓督，一副一勞永逸的姿態(tài)燃少。
我對原文的方法做了一些小調(diào)整和改變，具體實(shí)現(xiàn)如下：
- Visual Studio 2015铃在，新建個(gè)項(xiàng)目--》模板選SQL Server 數(shù)據(jù)庫項(xiàng)目阵具，命名項(xiàng)目sqlUtil
- 新建項(xiàng)--》 SQL CLR c# ==>SQL CLR c# 聚合 碍遍，是個(gè)類，命名StrJoin.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
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 StrJoin : IBinarySerialize
{
private StringBuilder sbIntermediate;
public void Init()
{
sbIntermediate = new StringBuilder();
}
public void Accumulate(SqlString Value,SqlString contChar)
{
if (Value == null || Value.ToString().ToLower().Equals("null"))
{
return;
}
else
{
sbIntermediate.Append(Value).Append(contChar);
}
}
public void Merge(StrJoin Group)
{
sbIntermediate.Append(Group.sbIntermediate);
}
public SqlString Terminate()
{
string output = String.Empty;
if (sbIntermediate != null && sbIntermediate.Length>0)
{
output = sbIntermediate.ToString(0, sbIntermediate.Length - 1);
}
return new SqlString(output);
}
// This is a place-holder member field
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
sbIntermediate = new StringBuilder(r.ReadString());
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(this.sbIntermediate.ToString());
}
#endregion
}
說明：看上去一臉蒙逼很復(fù)雜的樣子怕敬，其實(shí)以上函數(shù)有效的部分很簡單，重點(diǎn)部分就是
- 在Accumulate函數(shù)中：傳入?yún)?shù)帘皿，把字符串拼起來赖捌。
- 在Terminate函數(shù)中： 去掉最后一個(gè)連接符并輸出。
主要看這兩個(gè)動(dòng)作矮烹，就知道了。
在sqlserver中執(zhí)行如下：
--打開SQLSERVER的CLR功能
EXEC sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
GO
--注冊DLL
CREATE ASSEMBLY sqlUtil FROM 'C:\sqlUtil.dll' --生成的DLL路徑
GO
--注冊函數(shù)
CREATE AGGREGATE [dbo].[StrJoin] (@Value [nvarchar](MAX),
@contChar [nvarchar](2))
RETURNS [nvarchar](MAX)
EXTERNAL NAME [sqlUtil].[StrJoin]
這樣后罩锐，就可以愉快的使用了奉狈。
如果要更新dll，需要先drop涩惑，在create
順序是 刪除引用的函數(shù)-->刪除dll
DROP AGGREGATE StrJoin
DROP ASSEMBLY sqlUtil
PS:在這個(gè)過程遇到個(gè)糾結(jié)的問題仁期，就是虛擬機(jī)和遠(yuǎn)程機(jī)之間復(fù)制文件的時(shí)候，居然會(huì)有問題竭恬，導(dǎo)致一個(gè)更新的dll一直是舊版本跛蛋，而我卻以為代碼有錯(cuò)。痊硕。赊级。。最后用.Net Refector去看dll的函數(shù)岔绸，才驚覺這個(gè)問題理逊，吐血中.... 最后還是用共享傳的文件。
*** PS2：據(jù)說MYSQL和Oracle其實(shí)都有現(xiàn)成的group_contact 和 wm_concat盒揉，所以到了SQLSERVER2012,據(jù)說也支持了字符串聚合的函數(shù)晋被。但是在使用2012之前，等于是用第三種方法提前體驗(yàn)了而已刚盈。
