三张表如下:
select BuildingID,Shui from DataTable,用水数据表:DataTable
BuildingID Shui(用水吨数)
100001 5.0000
100002 8.0000
100003 12.0000
100004 18.0000
100005 25.0000
....
select BeginValue ,EndValue ,RateValue,FeeFunc from FeeRate,FeeRate表是水费费率表,即价格表,根据不同用水量,有不同的费率及不同的计算公式:
BeginValue EndValue RateValue(价格) FeeFunc(计算公式)
1.0000 10.25 1.25 Shui*ShuiDF_Shui
10.2500 20.50 2.50 10.25*ShuiDF_Shui+ShuiDF_Shui_1*(Shui-10.25)
20.5000 30.75 3.50 10.25*ShuiDF_Shui+ShuiDF_Shui_1*(Shui-10.25)+ShuiDF_Shui_2*(Shui-20.5)
待计算得到的费用表:
select BuildingID,Shui,ShuiFee from ShuidFValue,ShuidFValue费用表
BuildingID Shui ShuiFee
100001 5.0000 如何算出水费?
100002 8.0000 ?
100003 12.0000 ?
100004 18.0000 ?
100005 25.0000 ?
.....
此表的ShuiFee列的求值公式就是第2张表的3种公式之一,到底是哪一种,需要根据第1张表中的Shui的值,第1、3张表用BuildingID关联,即相等,唯一标识每一行。
试问如何通第1、2张表,得到第3张表中的字段ShuiFee的值,请大侠指点
select BuildingID,Shui from DataTable,用水数据表:DataTable
BuildingID Shui(用水吨数)
100001 5.0000
100002 8.0000
100003 12.0000
100004 18.0000
100005 25.0000
....
select BeginValue ,EndValue ,RateValue,FeeFunc from FeeRate,FeeRate表是水费费率表,即价格表,根据不同用水量,有不同的费率及不同的计算公式:
BeginValue EndValue RateValue(价格) FeeFunc(计算公式)
1.0000 10.25 1.25 Shui*ShuiDF_Shui
10.2500 20.50 2.50 10.25*ShuiDF_Shui+ShuiDF_Shui_1*(Shui-10.25)
20.5000 30.75 3.50 10.25*ShuiDF_Shui+ShuiDF_Shui_1*(Shui-10.25)+ShuiDF_Shui_2*(Shui-20.5)
待计算得到的费用表:
select BuildingID,Shui,ShuiFee from ShuidFValue,ShuidFValue费用表
BuildingID Shui ShuiFee
100001 5.0000 如何算出水费?
100002 8.0000 ?
100003 12.0000 ?
100004 18.0000 ?
100005 25.0000 ?
.....
此表的ShuiFee列的求值公式就是第2张表的3种公式之一,到底是哪一种,需要根据第1张表中的Shui的值,第1、3张表用BuildingID关联,即相等,唯一标识每一行。
试问如何通第1、2张表,得到第3张表中的字段ShuiFee的值,请大侠指点
字段 类型
BuildingID int
Shui decimal
表2:FeeRate
字段 类型
BeginValue: decimal
EndValue: decimal
RateValue: decimal
FeeFunc: char(300)
表3:huidFValue
字段 类型
BuildingID: int
Shui: decimal
ShuiFee: decimal
CREATE TABLE FeeRage([BeginValue] decimal(8,4), [EndValue] decimal(8,4), [RateValue] decimal(8,4), [FeeFunc] varchar(100))
INSERT INTO FeeRage
SELECT 0.0000, 10.25, 1.25, 'Shui*ShuiDF_Shui'
UNION ALL SELECT 10.2500, 20.50, 2.50, '10.25*ShuiDF_Shui+ShuiDF_Shui_1*(Shui-10.25)'
UNION ALL SELECT 20.5000, 30.75, 3.50, '10.25*ShuiDF_Shui+ShuiDF_Shui_1*(Shui-10.25)+ShuiDF_Shui_2*(Shui-20.5)'
GOCREATE TABLE ShuidFValue([BuildingID] int, [Shui] decimal(8,4), [ShuiFee] decimal(8,4))
INSERT INTO ShuidFValue
SELECT 100001, 5.0000, 0
UNION ALL SELECT 100002, 8.0000, 0
UNION ALL SELECT 100003, 12.0000, 0
UNION ALL SELECT 100004, 18.0000, 0
UNION ALL SELECT 100005, 25.0000, 0
GOCREATE FUNCTION CALC(@Shui DECIMAL(8, 4))
RETURNS DECIMAL(8, 4)
AS
BEGIN
DECLARE @Total DECIMAL(8, 4)
SELECT @Total = SUM(CASE WHEN @Shui < BeginValue THEN 0
WHEN @Shui BETWEEN BeginValue AND EndValue THEN (@Shui - BeginValue) * RateValue
WHEN @Shui >=EndValue THEN (EndValue - BeginValue) * RateValue END) FROM FeeRage
RETURN @Total
END
GOSELECT BuildingID, Shui, dbo.CALC(Shui) FROM ShuidFValueDROP TABLE FeeRage, ShuidFValue
DROP FUNCTION CALC
FROM DataTable d, FeeRate r
WHERE d.Shui BETWEEN r.BeginValue AND r.EndValue
RETURNS DECIMAL(8, 4)
AS
BEGIN
DECLARE @Total DECIMAL(8, 4)
SELECT @Total = SUM(CASE
WHEN @Shui < BeginValue THEN 0
WHEN @Shui BETWEEN BeginValue AND EndValue
THEN (@Shui - BeginValue) * RateValue
WHEN @Shui >=EndValue THEN (EndValue - BeginValue) * RateValue END)
FROM FeeRate
RETURN @Total
END
GOSELECT BuildingID, Shui, dbo.CALC(Shui) as ShuiFee
FROM DataTableDROP FUNCTION CALCBuildingID Shui ShuiFee
----------- ----------- ----------
100001 5.0000 6.2500
100002 8.0000 10.0000
100003 12.0000 17.1875
100004 18.0000 32.1875
100005 25.0000 54.1875(5 件処理されました)
SELECT d.BuildingID, d.Shui, r.FeeFunc
FROM DataTable d, FeeRate r
WHERE d.Shui BETWEEN r.BeginValue AND r.EndValue
中的r.FeeFunc只是得到正确的公式,没有得到具体的费用数据
FROM DataTable d, FeeRate r
WHERE d.Shui BETWEEN r.BeginValue AND r.EndValue
的r.FeeFunc列可以得到正确的计算公式中,但是不知如何将公式里的值给替换出来,在第3张表里或第1张表里都已经有数据及相关的数据了
WHEN Shui BETWEEN BeginValue AND EndValue THEN (Shui - BeginValue) * RateValue
WHEN Shui >=EndValue THEN (EndValue - BeginValue) * RateValue END)
FROM ShuidFValue, FeeRage
GROUP BY BuildingID, Shui
100001 5.0000 6.2500
100002 8.0000 10.0000
100003 12.0000 17.1875
100004 18.0000 32.1875
100005 25.0000 54.1875
但hhhdyj(萤火虫)大侠给的是:
100001 5.0000 7.60000000
100002 8.0000 13.30000000
100003 12.0000 23.08750000
100004 18.0000 41.98750000
100005 25.0000 65.61250000
最新的方法得到的结果如下
BuildingID Shui
----------- ---------- ----------------------------------------
100001 5.0000 6.25000000
100002 8.0000 10.00000000
100003 12.0000 17.18750000
100004 18.0000 32.18750000
100005 25.0000 54.18750000查询分析器里复制的
SELECT BuildingID, Shui, SUM(CASE WHEN Shui < BeginValue THEN 0
WHEN Shui BETWEEN BeginValue AND EndValue THEN (Shui - BeginValue) * RateValue
WHEN Shui >=EndValue THEN (EndValue - BeginValue) * RateValue END)
FROM ShuidFValue, FeeRage
GROUP BY BuildingID, Shui
去试试