自定义多参数Sql聚合函数
Creating a CLR user define aggregate (part 2). Use multiple columns in the aggregation function
In part 1 we created a nice user defined aggregate. Now we are going to make it more sophisticated and let its value depend on two parameters ShipCountry and ShipShipCity. You might try having two parameters in Accumulate function of the aggregate but you will get an error
The Accumulate method in user defined aggregate "Bonus" must have exactly one parameter.
We are definitely looking into adding “multi-column aggregates” feature in the future versions of SQL Server. For now you can use a workaround. The idea is to create a worker UDT that contain all the fields required for the aggregation. So if you want to take Orders.ShipCountry and Orders.ShipCity into account the UDT should have two corresponding fields. You also need to create a user defined function that takes a number of parameters and returns an instance of the worker UDT. And finally you create an aggregate that takes the worker UDT as a parameter in its aggregation function.
Let’s say XYZ wants to consider German sales that has been shipped to Berlin as regular sales. To take ShipCity this into account you first need to create a UDT. I won’t implement several methods to keep the sample short.
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize=8000)]
public struct OrderData : INullable, IBinarySerialize
{
public override string ToString()
{
throw new Exception("The method or operation is not implemented.");
}
public bool IsNull
{
get
{
return false;
}
}
&n
相关文档:
1 TOP
这是一个大家经常问到的问题,例如在SQLSERVER中可以使用如下语句来取得记录集中的前十条记录:
SELECT TOP 10 * from [index] ORDER BY indexid DESC;
但是这条SQL语句在SQLite中是无法执行的,应该改为:
SELECT * from [index] ORDER BY indexid DESC limit 0,10;
其中limit 0,10表示从第0条记录开始,往后 ......
SQL行转列的动态构造方法
/*假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果):
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
create t ......
--1:环境
服务器环境:
机器名称: ZehuaDb
操作系统:Windows 2000 Server
数据库版本:SQL 2000 Server 个人版
客户端
机器名称:Zlp
操作系统:Windows 2000 Server
数据库版本:SQL 2000 Server 个人版
--2:建用户帐号
在服务器端建立域用户帐号
我的电脑管理- ......
ALTER function dbo.UDF_SplitSubString
(
@Expression varchar(8000)
,@Delimiter varchar(100)
,@ int
)
returns varchar(8000)
as
begin
--declare @Expression varchar(8000)
--set @Expre ......