using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections.Generic;
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,
MaxByteSize=8000,
IsNullIfEmpty=true,
Name = "MiddleNumber")]
public class MiddleNumber:IBinarySerialize
{
public void Init()
{
list = new List<double>();
}
public void Accumulate(SqlDouble Value)
{
if (Value.IsNull == false)
list.Add(Value.Value);
}
public void Merge(MiddleNumber Group)
{
list.AddRange(Group.list);
}
public SqlDouble Terminate()
{
double re = 0;
int lCount = 0;
list.Sort();
lCount = list.Count;
if (lCount > 0)
{
if (lCount % 2 == 0)
{
re = (double)((Convert.ToDouble(list[lCount / 2 - 1]) + Convert.ToDouble(list[lCount / 2])) / 2.0);
}
else
{
re = (double)(Convert.ToDouble(list[Convert.ToInt32(Math.Floor(lCount / 2.0))]));
}
return new SqlDouble(re);
}
else
{
return SqlDouble.Null;
}
}
public void Write(BinaryWriter writer)
{
int lCount = list.Count;
writer.Write(lCount);
if (lCount > 0)
{
foreach (double number in list)
{
writer.Write(number);
}
}
}
public void Read(BinaryReader reader)
{
int lCount = reader.ReadInt32();
list = new List<double>();
for (int i = 0; i < lCount; i++)
{
list.Add(reader.ReadDouble());
}
}
// 这是占位符成员字段
private List<double> list;
}
sql server management studio
CREATE ASSEMBLY ass_test
FROM 'G:\CLR_Test\SqlServerProject5\SqlServerProject5\bin\Debug\SqlServerProject5.dll';
GO
CREATE AGGREGATE MiddleNumber(@number float)
RETURNS float
EXTERNAL NAME ass_test.MiddleNumber;
GO
declare @t table(id int,val float)
insert @t select 1,20.2
union all select 1,50
union all select 1,20
union all select 2,50
union all select 2,20
union all select 3,null
union all select 4,0
--查询
select id,dbo.MiddleNumber(val) as 中位数
from @t
group by id
/*
id 中位数
----------- ----------------------
1 20.2
2 35
3 NULL
4 0
(4 行受影响)
*/
