sql-server - 面额分配计算
问题描述
一些背景故事:A 公司向挑战的获胜者发放代金券。我当前正在编写的 SQL 需要确定所需的凭证面额,该面额总和为授予一个人的价值。我有一个表格,其中存储了可用于代金券的面额,具体取决于国家和货币。
在下面的示例中,一个特定的人将获得价值 80 欧元的代金券。
下面的查询显示特定国家可用凭证面额的查找表的结果。
SELECT * FROM tblDenominationScheme WHERE CountryCode IN ('AT', 'US')
结果:
No. | CountryCode | VoucherName | VoucherValue
-------------------------------------------------
1 | AT | €50 Shop A | 50
2 | AT | €25 Shop A | 25
3 | AT | €15 Shop A | 15
4 | AT | €10 Shop A | 10
5 | US | $50 Store B | 50
6 | US | $10 Store B | 10
7 | US | $5 Store B | 5
我当前的 SQL 如下确定 80 欧元代金券所需的代金券面额:
DECLARE @CountryCode1 VARCHAR(2) = 'AT'
DECLARE @ChallengerID INT = 1172
DECLARE @RoundedAmount1 INT = 80
DECLARE @Vouchers INT
DECLARE @AmountAwarded INT = 0
SET @AmountAwarded = @RoundedAmount1
DROP TABLE IF EXISTS #tempVoucher
CREATE TABLE #tempVoucher
(
CountryCode VARCHAR(2),
ChallengerID INT,
AmountAwarded INT,
Vouchers INT,
)
WHILE (@RoundedAmount1 > 0)
BEGIN
SET @Vouchers = 0
SELECT TOP 1 @Vouchers = VoucherValue FROM tblDenominationScheme WHERE CountryCode = @CountryCode1 AND VoucherValue <= @RoundedAmount1 ORDER BY VoucherValue DESC
IF (@Vouchers > 0)
BEGIN
SET @RoundedAmount1 = @RoundedAmount1 - @Vouchers
END
ELSE
BEGIN
SELECT TOP 1 @Vouchers = VoucherValue FROM tblDenominationScheme WHERE CountryCode = @CountryCode1 ORDER BY VoucherValue
SET @RoundedAmount1 = @RoundedAmount1 - @RoundedAmount1
END
INSERT INTO #tempVoucher VALUES (@CountryCode1,@ChallengerID, @AmountAwarded, @Vouchers)
END
SELECT * FROM #tempVoucher
上述 SQL 的结果:
No. | CountryCode | ChallengerID | AmountAwarded | Vouchers
--------------------------------------------------------------
1 | AT | 1172 | 80 | 50
2 | AT | 1172 | 80 | 25
3 | AT | 1172 | 80 | 10
注意:AmountAwarded 列中的值对于所有 3 行都是相同的。3 行的 Vouchers 列中的金额总计应为 80。
上面的结果显然是不正确的,因为如果你将 Vouchers 列中的值相加,它会给你 85,比 AmountAwarded 多 5
预期结果(或至少最接近):
No. | CountryCode | ChallengerID | AmountAwarded | Vouchers
--------------------------------------------------------------
1 | AT | 1172 | 80 | 50
2 | AT | 1172 | 80 | 10
3 | AT | 1172 | 80 | 10
4 | AT | 1172 | 80 | 10
有谁能帮忙吗?
解决方案
这可能是一个昂贵的查询,但可以为您提供不同的选项,最多可提供 7 张代金券,以获得预期的结果。但是,如果行增加或凭证数量可能更大,这将产生大量读取。
DECLARE @CountryCode1 VARCHAR(2) = 'AT'
DECLARE @RoundedAmount1 INT = 80;
WITH cteDenominations AS(
SELECT No, VoucherValue
FROM tblDenominationScheme
WHERE CountryCode = @CountryCode1
UNION ALL
SELECT 10000, 0
),
ctePermutations AS(
SELECT a.No AS a_No,
a.VoucherValue AS a_Value,
b.No AS b_No,
b.VoucherValue AS b_Value,
c.No AS c_No,
c.VoucherValue AS c_Value,
d.No AS d_No,
d.VoucherValue AS d_Value,
e.No AS e_No,
e.VoucherValue AS e_Value,
f.No AS f_No,
f.VoucherValue AS f_Value,
g.No AS g_No,
g.VoucherValue AS g_Value,
ROW_NUMBER() OVER(ORDER BY a.No, b.No, c.No, d.No) Permutation
FROM cteDenominations a
JOIN cteDenominations b ON a.VoucherValue >= b.VoucherValue
JOIN cteDenominations c ON b.VoucherValue >= c.VoucherValue
JOIN cteDenominations d ON c.VoucherValue >= d.VoucherValue
JOIN cteDenominations e ON d.VoucherValue >= e.VoucherValue
JOIN cteDenominations f ON e.VoucherValue >= f.VoucherValue
JOIN cteDenominations g ON f.VoucherValue >= g.VoucherValue
WHERE @RoundedAmount1 = a.VoucherValue
+ b.VoucherValue
+ c.VoucherValue
+ d.VoucherValue
+ e.VoucherValue
+ f.VoucherValue
+ g.VoucherValue
)
SELECT Permutation,
u.No,
u.VoucherValue
FROM ctePermutations
CROSS APPLY (VALUES(a_No, a_Value),
(b_No, b_Value),
(c_No, c_Value),
(d_No, d_Value),
(e_No, e_Value),
(f_No, f_Value),
(g_No, g_Value))u(No, VoucherValue)
WHERE VoucherValue > 0
AND Permutation = 1 --Remove this to get all possibilities
;
推荐阅读
- java - 如何跳转到下一个 TODO 存根?
- django - Django链接多个表
- swift - 我如何在不将此选项卡视为项目的情况下在 UITabBar 中实现选项卡?
- git - 如何在 gitbash 中打开记事本++?
- amazon-web-services - 如何使 SAM 模板文件适用于两种不同的环境?
- office-js - 使用 office-js 在 Outlook 插件上实现自定义发送邮件按钮
- ios - 使用触控板在 TableView 上滚动
- angular - 无法使用表单组中的嵌套表单数组正确显示表单
- typescript - 静态方法不是函数:Typescript。Vue
- python - 在 SQLite 查询中比较两个可能为 NULL 的值