可以通过将当前项目的空间限制为未花费的资金来删除大量数据。
在我的家庭系统上,运行需要花费2.6秒至2.8秒的时间。在SQLFiddle上,前几次运行可能需要更长时间,然后稳定在1.8秒左右。
WITH Unit(N) AS (
SELECT N
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) t(N)
), Counter(N) AS (
SELECT u.n + 10*te.n + 100*hu.n
FROM Unit u CROSS JOIN Unit te CROSS JOIN Unit hu
WHERE u.n + 10*te.n + 100*hu.n <= (SELECT 1000 / Min(Price) FROM Sample))
SELECT N INTO #Counter FROM Counter;
WITH Products AS (
SELECT [Pen], [DVD], [PenDrive], [Mouse], [TV]
FROM (SELECT product, price FROM sample) s PIVOT
(MAX(price) FOR product IN ([Pen], [DVD], [PenDrive], [Mouse], [TV])) p
)
SELECT cP.N Pen, cD.N DVD, cPe.N PenDrive, cM.N Mouse
, CAST((1000 - p.pen * cP.N - p.DVD * cD.N
- p.PenDrive * cPe.N - p.Mouse * cM.N) / p.TV as INT) TV
, Money = p.pen * cP.N + p.DVD * cD.N + p.PenDrive * cPe.N
+ p.Mouse * cM.N
+ p.TV * CAST((1000 - p.pen * cP.N - p.DVD * cD.N
- p.PenDrive * cPe.N - p.Mouse * cM.N) / p.TV as INT)
From Products p
LEFT Join #Counter cP ON cP.N <= (1000 / p.Pen)
LEFT Join #Counter cD ON cD.N <= ((1000 - p.pen * cP.N) / p.DVD)
LEFT Join #Counter cPe
ON cPe.N <= ((1000 - p.pen * cP.N - p.DVD * cD.N) / p.PenDrive)
LEFT Join #Counter cM
ON cM.N <= ((1000 - p.pen * cP.N - p.DVD * cD.N
- p.PenDrive * cPe.N) / p.Mouse)
WHERE p.pen * cP.N + p.DVD * cD.N
+ p.PenDrive * cPe.N + p.Mouse * cM.N
+ p.TV * CAST((1000 - p.pen * cP.N - p.DVD * cD.N - p.PenDrive * cPe.N
- p.Mouse * cM.N) / p.TV as INT) = 1000
变更内容
#Counter
现在是一个临时表,它只计算一次
- 各种产品
CTE
已经被替换为样本表旋转
- 产品 CTE 中的
CROSS JOIN
已经消失,它们仍然存在于主查询中,但减少了四个 CROSS JOIN
总是好的
TOP
已经消失,WHERE
条件负责仅显示完美解决方案
- 在主查询中,我们现在有
LEFT JOIN
... 不对,它们仍然是 CROSS JOIN
,LEFT JOIN
是用来限制行数的 ON
子句不存在的情况下使用的
工作原理
产品价格未旋转,使得可以通过“名称”(列名)获取产品价格。
FROM
块的工作方式类似于4个缩进的FOR
,其中(1000-已经花费)/价格子句将计数器限制在不超过1000美元的值上。
最后一个产品总是通过差异(剩余多少$ / 价格)计算,完全删除CROSS JOIN
SQLFiddle演示,总金额为1000美元。
根据提供的数据,有3531个解决方案。
旧回答
如果您想让服务器在您午餐的所有时间运行,这是一个愚蠢的解决方案。
请注意,此解决方案探索了问题的所有空间,因此性能最好也很差。
WITH Base(N) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1)
, Unit(N) AS (
SELECT Row_Number() Over (ORDER BY (SELECT NULL)) - 1
FROM Base)
, Counter(N) AS (
SELECT u.n + 10*te.n + 100*hu.n + 1000*th.n
FROM Unit u
CROSS JOIN Unit te
CROSS JOIN Unit hu
CROSS JOIN Unit th
WHERE u.n + 10*te.n + 100*hu.n + 1000*th.n <= (SELECT 1000 / Min(Price)
FROM Sample))
, Pens AS (
SELECT product, Price = price * N, Quantity = N
FROM sample CROSS JOIN Counter
WHERE product = 'Pen' AND N <= 1000 / Price)
, DVDs AS (
SELECT product, Price = price * N, Quantity = N
FROM sample CROSS JOIN Counter
WHERE product = 'DVD' AND N <= 1000 / Price)
, Pendrives AS (
SELECT product, Price = price * N, Quantity = N
FROM sample CROSS JOIN Counter
WHERE product = 'Pendrive' AND N <= 1000 / Price)
, Mouses AS (
SELECT product, Price = price * N, Quantity = N
FROM sample CROSS JOIN Counter
WHERE product = 'Mouse' AND N <= 1000 / Price)
, TVs AS (
SELECT product, Price = price * N, Quantity = N
FROM sample CROSS JOIN Counter
WHERE product = 'TV' AND N <= 1000 / Price
)
SELECT TOP 10
Pen = p.Quantity
, DVD = d.Quantity
, Pendrive = pe.Quantity
, Mouse = m.Quantity
, TV = t.Quantity
, Price = p.Price + d.price + pe.price + m.price + t.price
FROM pens p
CROSS JOIN DVDs d
CROSS JOIN Pendrives pe
CROSS JOIN Mouses m
CROSS JOIN TVs t
WHERE p.Price + d.price + pe.price + m.price + t.price <= 1000
ORDER BY p.Price + d.price + pe.price + m.price + t.price DESC
SQLFiddle演示,总金额为100美元(运行大约需要2秒钟)
SQLFiddle演示,总金额为200美元(运行大约需要6秒钟)
1000美元的演示可能会导致超时
工作原理
- Base用作Unit的基础。
- Unit从0到9计数
- Counter使用Unit从0到9999计数,或者根据您想花费的更便宜物品的价格除以价格所施加的限制。
- 每个项目都有自己的CTE来计算该项目在您的资本内任意次数的价格。
- 产品CTE交叉连接以检查货币限制内的每种组合。
- TOP 10在这里是因为可能会有多个组合使用了完全相同的金额。
只是说,是的,你可以在SQLServer中设计解决方案,这甚至不难,但这并不意味着你应该这样做。