此類問題還可以延伸到類似進銷存的批次計算中，這也要關(guān)注其他歷史記錄來決定當前某條記錄的狀態(tài)。
sql語句無法簡單實現(xiàn)mdx語句的類似功能，必須得用交叉表關(guān)聯(lián)來對比。這里我們用CLR函數(shù)來實現(xiàn)mdx語句的類似語法。在select的時候把得到過的做個緩存就可以了。效率應該可以提高不少。
clr的代碼如下，編譯為TestFun.dll，復制到sql服務器的文件目錄下。
--------------------------------------------------------------------------------
復制代碼 代碼如下:
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>
/// 獲取當前組上條記錄數(shù)值
/// /summary>
/// param name="key"> 并發(fā)鍵 /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>
/// 初始化并發(fā)鍵
/// /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>
/// 釋放并發(fā)鍵
/// /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 ;
}
}
};
--------------------------------------------------------------------------------
部署和生成自定義函數(shù)，其中考慮到并發(fā)，我們還是需要一個并發(fā)鍵來表達當前查詢
--------------------------------------------------------------------------------
復制代碼 代碼如下:
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
--------------------------------------------------------------------------------
這樣我們就可以使用了，測試腳本如下， xfn_GetPrevMemberValue就是獲取上月價格的函數(shù)。
--------------------------------------------------------------------------------
-- 建立測試環(huán)境
復制代碼 代碼如下:
declare @t table (
[ 區(qū)域 ] [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 區(qū)域 , TradeMonth, TradePrice, LastMonthPrice,
cast ( round (( Tradeprice- LastMonthPrice)* 100/ LastMonthPrice, 2) as varchar ( 10))+ '%' as 環(huán)比 from (
select *, cast ( dbo. xfn_GetPrevMemberValue( @key, 區(qū)域 , Tradeprice) as float ) as LastMonthPrice from @t
) t
select @b= dbo. xfn_disposeKey( @key)
-- 結(jié)果
/*
區(qū)域 TradeMonth TradePrice LastMonthPrice 環(huán)比
---- ---------- ---------------------- ---------------------- -----------
閔行 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%
*/
--------------------------------------------------------------------------------
這個函數(shù)寫的還是比較粗糙，如果進一步改進還可以詳細定義如何獲取上一個維度的方法。這里只是根據(jù)查詢順序來做緩存。感興趣的朋友可以完善一下。
您可能感興趣的文章:- 通過SQLSERVER重啟SQLSERVER服務和計算機的方法
- 利用php+mysql來做一個功能強大的在線計算器
- 在php和MySql中計算時間差的方法
- mysql 字符串長度計算實現(xiàn)代碼(gb2312+utf8)
- SQLSERVER 根據(jù)地圖經(jīng)緯度計算距離差示例
- 用sql實現(xiàn)18位身份證校驗代碼分享 身份證校驗位計算
- SQL計算字符串中最大的遞增子序列的方法
- SQL語句計算兩個日期之間有多少個工作日的方法
- 如何計算多個訂單的核銷金額
