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;

}

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 行受影响)
*/