任意指定金额，计算满足条件的明细

[X2ZuoWangZhan]

请教一下各位大神，我想用Sql计算某个和值的,它的明细组成方案。比如我想查30元的方案。怎么计算出有多少种组成方案。商品个数不用管.分别的方案组成是什么样的

商品名称 零售价
1 0.1
2 0.5
3 0.5
4 0.5
5 1
6 1
7 1
8 2
9 2
10 2
11 5
12 5
13 5
14 10
15 10
16 10
17 20
18 20
19 20
20 50
21 50
22 50
23 100
24 100
25 100

[shoppo0505]

引用 1 楼 zjcxc 的回复:

WITH DATA AS(
SELECT * FROM(VALUES
(1, 0.1),
(2, 0.5),
(3, 0.5),
(4, 0.5),
(5, 1),
(6, 1),
(7, 1),
(8, 2),
(9, 2),
(10, 2),
(11, 5),
(12, 5),
(13, 5),
(14, 10),
(15, 10),
(16, 10),
(17, 20),
(18, 20),
(19, 20),
(20, 50),
(21, 50),
(22, 50),
(23, 100),
(24, 100),
(25, 100)
) D(商品名称, 零售价 )
),
Q AS(
	SELECT DATA.*, 
		CONVERT(nvarchar(max), RTRIM(DATA.商品名称)) AS 商品组合,
		CONVERT(decimal(18,2), DATA.零售价) AS 组合价,
		DATA.商品名称 as 最后组合商品
	FROM DATA
	WHERE DATA.零售价 <= 30
	UNION ALL
	SELECT DATA.*, 
		Q.商品组合 + ', ' + RTRIM(DATA.商品名称) AS 商品组合,
		CONVERT(decimal(18, 2), Q.组合价 + DATA.零售价) AS 组合价,
		DATA.商品名称 as 最后组合商品
	FROM DATA, Q
	WHERE Q.组合价 + DATA.零售价 <= 30
		AND Q.最后组合商品 < DATA.商品名称
)R
SELECT * FROM Q WHERE 组合价 = 30

学习了/ 不过这个也不对吧，3个16号物品也符合要求，但是你这个脚本的答案不包括这种结果。

[zjcxc]

WITH DATA AS(
SELECT * FROM(VALUES
(1, 0.1),
(2, 0.5),
(3, 0.5),
(4, 0.5),
(5, 1),
(6, 1),
(7, 1),
(8, 2),
(9, 2),
(10, 2),
(11, 5),
(12, 5),
(13, 5),
(14, 10),
(15, 10),
(16, 10),
(17, 20),
(18, 20),
(19, 20),
(20, 50),
(21, 50),
(22, 50),
(23, 100),
(24, 100),
(25, 100)
) D(商品名称, 零售价 )
),
Q AS(
	SELECT DATA.*, 
		CONVERT(nvarchar(max), RTRIM(DATA.商品名称)) AS 商品组合,
		CONVERT(decimal(18,2), DATA.零售价) AS 组合价,
		DATA.商品名称 as 最后组合商品
	FROM DATA
	WHERE DATA.零售价 <= 30
	UNION ALL
	SELECT DATA.*, 
		Q.商品组合 + ', ' + RTRIM(DATA.商品名称) AS 商品组合,
		CONVERT(decimal(18, 2), Q.组合价 + DATA.零售价) AS 组合价,
		DATA.商品名称 as 最后组合商品
	FROM DATA, Q
	WHERE Q.组合价 + DATA.零售价 <= 30
		AND Q.最后组合商品 < DATA.商品名称
)R
SELECT * FROM Q WHERE 组合价 = 30

[卖水果的net]

引用 2 楼 shoppo0505 的回复:

学习了/ 不过这个也不对吧，3个16号物品也符合要求，但是你这个脚本的答案不包括这种结果。[/quote]         AND Q.最后组合商品 < DATA.商品名称 改成 <= 就可以了。 zjcxc 应该就是写个例子，剩下的让楼主自己发挥。

[中国风]

这类穷举，也就两个方向递增和递减 组合价--递减，以传参数量为30 这样改 e.g.

Q AS(
	SELECT DATA.*, 
		CONVERT(nvarchar(max), RTRIM(DATA.商品名称)) AS 商品组合,
		CONVERT(decimal(18,2),30.00- DATA.零售价) AS 组合价,
		DATA.商品名称 as 最后组合商品
	FROM DATA
	WHERE DATA.零售价 <= 30
	UNION ALL
	SELECT DATA.*, 
		Q.商品组合 + ', ' + RTRIM(DATA.商品名称) AS 商品组合,
		CONVERT(decimal(18,2),组合价 -DATA.零售价) AS 组合价,
		DATA.商品名称 as 最后组合商品
	FROM DATA, Q
	WHERE Q.组合价 -DATA.零售价 >= 0
		AND Q.最后组合商品 < DATA.商品名称
)
SELECT * FROM Q WHERE 组合价 = 0

[中国风]

如果要考虑数量，那又是另一种玩法，需要加上循环次数,不然组合太多,写个例子 e.g.

DECLARE @组合价 MONEY=30,@LoopCounted TINYINT=3
;WITH D AS(
SELECT * FROM(VALUES
(1, 0.1),
(2, 0.5),
(3, 0.5),
(4, 0.5),
(5, 1),
(6, 1),
(7, 1),
(8, 2),
(9, 2),
(10, 2),
(11, 5),
(12, 5),
(13, 5),
(14, 10),
(15, 10),
(16, 10),
(17, 20),
(18, 20),
(19, 20),
(20, 50),
(21, 50),
(22, 50),
(23, 100),
(24, 100),
(25, 100)
) D(商品名称, 零售价 )
),
Q AS(
	SELECT D.*, 
		CONVERT(nvarchar(max), RTRIM(D.商品名称)) AS 商品组合,
		CONVERT(decimal(18,2),30.00- D.零售价) AS 组合价,
		D.商品名称 as 最后组合商品,
		@LoopCounted-1 AS LoopCounted
	FROM D
	WHERE D.零售价 <= 30.00
	UNION ALL
	SELECT D.*, 
		Q.商品组合 + ', ' + RTRIM(D.商品名称) AS 商品组合,
		CONVERT(decimal(18,2),组合价-D.零售价) AS 组合价,
		D.商品名称 as 最后组合商品,
		LoopCounted=Q.LoopCounted-1
	FROM D, Q
	WHERE Q.组合价  >0 AND D.零售价>=Q.零售价 AND Q.LoopCounted>0
)
SELECT * FROM Q WHERE 组合价 = 0