作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
一些背景故事:A 公司向挑战的获胜者发放代金券。我目前正在编写的 SQL 需要确定所需的凭证面额,该面额总和为授予某人的值(value)。我有一张表,根据国家和货币存储可用于代金券的面额。
在下面的示例中,特定的人获得了值(value) 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
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
No. | CountryCode | ChallengerID | AmountAwarded | Vouchers
--------------------------------------------------------------
1 | AT | 1172 | 80 | 50
2 | AT | 1172 | 80 | 25
3 | AT | 1172 | 80 | 10
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
;
关于sql-server - 面额分布计算,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/59146565/
我有一个像这样的对象: var currencyTypes = { NOK: {value:1.00000, name: "Norske kroner", denomination: "k
我是一名优秀的程序员,十分优秀!