排序字符串的 SQL Server CLR 函数聚合

Question

为了得到一个排序的聚合字符串，我在下面编写了CLR函数。但是，它总是返回空而不是我预期的，就像"001， 002， 003"一样。我尝试在Visual Studio 2017中调试CLR函数，但抛出了错误消息

无法完成操作。未指定的错误

法典：

[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
Name = "CLRSortedCssvAgg", //aggregate name on sql
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
IsNullIfEmpty = false, //optimizer property
MaxByteSize = -1) //maximum size in bytes of persisted value
]
public class SortedCssvConcatenateAgg : IBinarySerialize
{
/// <summary>
/// The variable that holds all the strings to be aggregated.
/// </summary>
List<string> aggregationList;
StringBuilder accumulator;
/// <summary>
/// Separator between concatenated values.
/// </summary>
const string CommaSpaceSeparator = ", ";
/// <summary>
/// Initialize the internal data structures.
/// </summary>
public void Init()
{
accumulator = new StringBuilder();
aggregationList = new List<string>();
}
/// <summary>
/// Accumulate the next value, not if the value is null or empty.
/// </summary>
public void Accumulate(SqlString value)
{
if (value.IsNull || String.IsNullOrEmpty(value.Value))
{
return;
}
aggregationList.Add(value.Value);
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(SortedCssvConcatenateAgg other)
{
aggregationList.AddRange(other.aggregationList);
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
if (aggregationList != null && aggregationList.Count > 0)
{
aggregationList.Sort();
accumulator.Append(string.Join(CommaSpaceSeparator, aggregationList));
aggregationList.Clear();
}
return new SqlString(accumulator.ToString());
}
public void Read(BinaryReader r)
{
accumulator = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(accumulator.ToString());
}
}

Answer 1

你很接近。只需要一些小的调整。执行以下操作，它将起作用(我测试过(：

1. 删除对accumulator的所有引用。它没有被使用。

2. 将 Terminate((、Read(( 和 Write(( 方法替换为以下内容：

   public SqlString Terminate()
   {
   string _Aggregation = null;
   if (aggregationList != null && aggregationList.Count > 0)
   {
   aggregationList.Sort();
   _Aggregation = string.Join(CommaSpaceSeparator, aggregationList);
   }
   return new SqlString(_Aggregation);
   }
   public void Read(BinaryReader r)
   {
   int _Count = r.ReadInt32();
   aggregationList = new List<string>(_Count);
   for (int _Index = 0; _Index < _Count; _Index++)
   {
   aggregationList.Add(r.ReadString());
   }
   }
   public void Write(BinaryWriter w)
   {
   w.Write(aggregationList.Count);
   foreach (string _Item in aggregationList)
   {
   w.Write(_Item);
   }
   }
   

---

也就是说，我不确定这种方法是比FOR XML方法更快还是更慢，但 UDA 肯定会使查询更具可读性，尤其是在您需要多个聚合的情况下。

不过，我应该提到，从SQL Server 2017开始，这变成了一个内置函数：STRING_AGG(允许通过WITHIN GROUP (ORDER BY ... )子句进行排序(。

Answer 2

在你的Accumulate和Merge中，你正在处理你的aggregationList;在Read和Write中，你正在处理accumulator。您应该为所有这些选择一个或另一个并使用它。据我了解，当引擎需要将临时结果保存到工作表时，会使用Read和Write。对于您的情况，当它这样做时，它只保留您的空 StringBuilder。