34,575
社区成员
发帖
与我相关
我的任务
分享
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
//保存当前组当前值
private static System.Collections.Generic.Dictionary<string, SqlString> _listValue = new System.Collections.Generic.Dictionary<string, SqlString>();
//保存当前组
private static System.Collections.Generic.Dictionary<string, string> _listGroup = new System.Collections.Generic.Dictionary<string, string>();
/// <summary>
/// 获取当前组上条记录数值
/// </summary>
/// <param name="key">并发键</param>
/// <param name="currentGroup">当前组</param>
/// <param name="currentValue">当前组当前值</param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetPrevMemberValue(SqlString key,SqlString currentGroup,SqlString currentValue)
{
if (key.IsNull || currentGroup.IsNull) return SqlString.Null;
try
{
SqlString prevMemberValue = _listValue[key.Value];
//组变更
if (_listGroup[key.Value] != currentGroup.Value)
{
prevMemberValue = SqlString.Null;
_listGroup[key.Value] = currentGroup.Value;
}
//值变更
_listValue[key.Value] = currentValue;
return prevMemberValue;
}
catch
{
return SqlString.Null;
}
}
/// <summary>
/// 初始化并发键
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean InitKey(SqlString key)
{
try
{
_listValue.Add(key.Value, SqlString.Null);
_listGroup.Add(key.Value, string.Empty);
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 释放并发键
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean DisposeKey(SqlString key)
{
try
{
_listValue.Remove(key.Value);
_listGroup.Remove(key.Value);
return true;
}
catch
{
return false;
}
}
};
CREATE ASSEMBLY TestForSQLCLR FROM 'E:\sqlclrdata\TestFun.dll' WITH PERMISSION_SET = UnSAFE;
--
go
--获取上个维度
CREATE FUNCTION dbo.xfn_GetPrevMemberValue
(
@key nvarchar(255),
@initByDim nvarchar(255),
@currentValue nvarchar(255)
)
RETURNS nvarchar(255)
AS EXTERNAL NAME TestForSQLCLR.[UserDefinedFunctions].GetPrevMemberValue
go
--初始化并发键
CREATE FUNCTION dbo.xfn_initKey
(
@key nvarchar(255)
)
RETURNS bit
AS EXTERNAL NAME TestForSQLCLR.[UserDefinedFunctions].InitKey
go
--清理并发键
CREATE FUNCTION dbo.xfn_disposeKey
(
@key nvarchar(255)
)
RETURNS bit
AS EXTERNAL NAME TestForSQLCLR.[UserDefinedFunctions].DisposeKey
--建立测试环境
declare @t table(
[区域] [varchar](4) COLLATE Chinese_PRC_CI_AS NULL,
[TradeMonth] [varchar](7) COLLATE Chinese_PRC_CI_AS NULL,
[TradeMoney] [float] NULL,
[TradeArea] [float] NULL,
[TradePrice] [float] NULL
)
insert into @t
select '闵行','2007-03','2125714.91','241.65','8796.67' union
select '闵行','2007-04','8408307.64','907.32','9267.19' union
select '闵行','2007-05','10230321.95','1095.88','9335.26' union
select '浦东','2007-01','12738432.25','1419.05','8976.73' union
select '浦东','2007-02','4970536.74','395.49','12568.05' union
select '浦东','2007-03','5985405.76','745.94','8023.98' union
select '浦东','2007-04','21030788.61','1146.89','18337.23' union
select '普陀','2007-01','1863896','161.39','11549.02' union
select '普陀','2007-02','1614015','119.59','13496.24' union
select '普陀','2007-03','1059235.19','135.21','7834'
--测试语句
declare @key varchar(40)
declare @b bit
set @key=newid()
select @b=dbo.xfn_initKey(@key)
select 区域,TradeMonth,TradePrice,LastMonthPrice,
cast(round((Tradeprice-LastMonthPrice)*100/LastMonthPrice,2) as varchar(10))+'%' as 环比 from (
select *,cast(dbo.xfn_GetPrevMemberValue(@key,区域,Tradeprice) as float) as LastMonthPrice from @t
)t
select @b=dbo.xfn_disposeKey(@key)
--结果
/*
区域 TradeMonth TradePrice LastMonthPrice 环比
---- ---------- ---------------------- ---------------------- -----------
闵行 2007-03 8796.67 NULL NULL
闵行 2007-04 9267.19 8796.67 5.35%
闵行 2007-05 9335.26 9267.19 0.73%
浦东 2007-01 8976.73 NULL NULL
浦东 2007-02 12568.05 8976.73 40.01%
浦东 2007-03 8023.98 12568 -36.16%
浦东 2007-04 18337.23 8023.98 128.53%
普陀 2007-01 11549.02 NULL NULL
普陀 2007-02 13496.24 11549 16.86%
普陀 2007-03 7834 13496.2 -41.95%
*/