满足使用 mysql 所需的数量 [英] fulfilling quantity required using mysql
问题描述
我正在使用 mysql 和 php，我需要弄清楚如何满足特定请求的数量:如果这是我的请求表:
I am working with mysql and php and I need to work out how to fulfil a quantity for a particular request: if this is my request table:
request id[pk], custID[fk from customer table], productid[fk in products table], quantites_requested, price_requested这是advertised_products 表:
request id[pk], custID[fk from customer table], productid[fk in products table], quantites_requested, price_requested
this is the advertised_products table:
advertiseid[pk]、customerid[fk from customer table]、productid[fk in products table]、quantites_advertised、price_advertised
如果我的请求表中有这条记录:
if I have this record in my request table:
1,1,1,10.0,5.00
以及以下广告数量:
1,2,1,4.00,2.00 -- This means customer id 2 is selling choclates(product id 1), for 4KG's @£2.00
2,3,1,5.00,2.50
3,4,1,1.00,1.00
4,5,1,10.00,6.00
我怎样才能使用 sql select 语句，以便:我可以弥补客户的请求数量?
How would I be able to use an sql select statement so that: I can make up the customer's request quantity?
我希望能够选择构成请求数量的最便宜的总数.
I wanted to be able to select the cheapest total that makes up the requested quantity.
因此，在这种情况下，输出将显示:
so, in this case the output would show:
2,1,4.00,2.00
3,1,5.00,2.50
4,1,1.00,1.00
所以客户 2,3 和 4 将显示为他们的数量 4+5+1=10.0 构成了请求的数量......而且它也比卖家编号 5 便宜.
so customer 2,3 and 4 would be displayed as their quantities of 4+5+1=10.0 makes up the requested quantity... and it is also cheaper then seller number 5.
是否有任何这样的查询我可以使用，以便我能够查找 SQL 表并将数量累加到请求的数量，然后检查哪个是最便宜的使用?
Is there any such query I can use so that I would be able to look up the SQL table and cumulatively add up the quantities until the requested amount and then check which is the cheapest one to use?
感谢您的帮助和时间.
推荐答案
这类似于我的 问题.
正如@Origin 在回答中指出的，这是一个 设置覆盖问题 并且是一个NP-Complete 问题，即除了非常少的可能性.
As pointed out in the answer by @Origin this is a set coverage problem and is an NP-Complete problem i.e. uncomputable for any but a very small number of possibilities.
对于您的问题，贪心算法解决方案可能就足够了.
For your problem a greedy algorithm solution may be adequate.
我没有时间解决您的问题，但下面发布的是我用来解决我的问题的代码.您的表结构更简单，因此您的解决方案也应该如此！
I don't have time to solve your problem but posted below is the code that I used to solve mine. Your table structure is simpler so your solution should be too!
DECLARE @GreedySetCover table
(
Location_Id int
,Supplier_Id int
,Ranking int
)
INSERT INTO @GreedySetCover
--Include Suppliers who are sole suppliers for any item
SELECT ss.Location_Id
,si.Supplier_Id
,0 Ranking
FROM (
SELECT pr.Location_Id
,pr.Item_Id
FROM PartsRequests pr
INNER JOIN
SupplierItems si ON pr.Item_Id=si.Item_Id
WHERE pr.Order_Id IS NULL
GROUP BY pr.Location_Id
,pr.Item_Id
HAVING COUNT(*)=1
) ss
INNER JOIN
SupplierItems si ON si.Item_Id=ss.Item_Id
UNION
--Include suppliers who do not charge a delivery fee
SELECT pr.Location_Id
,si1.Supplier_Id
,0 Ranking
FROM PartsRequests pr
INNER JOIN
SupplierItems si1 ON si1.Item_Id=pr.Item_Id
WHERE pr.Order_Id IS NULL
AND
(
NOT EXISTS
(SELECT ISNULL(si2.AmountPerOrder,0)
FROM SupplierItems si2
WHERE si1.Supplier_Id=si2.Supplier_Id
AND
si2.Item_Id=0)
OR
(SELECT ISNULL(si2.AmountPerOrder,0)
FROM SupplierItems si2
WHERE si1.Supplier_Id=si2.Supplier_Id
AND
si2.Item_Id=0
)=0
)
DECLARE @Ranking int = 1
--While any item does not have a supplier
WHILE EXISTS
(
SELECT pr.Location_Id
,pr.Item_Id
FROM PartsRequests pr
EXCEPT
(
SELECT gsc.Location_Id
,pr1.Item_Id
FROM @GreedySetCover gsc
INNER JOIN
SupplierItems si ON gsc.Supplier_Id=si.Supplier_Id
INNER JOIN
PartsRequests pr1 ON pr1.Item_Id=si.Item_Id AND pr1.Location_Id=gsc.Location_Id
WHERE pr1.ORDER_ID IS NULL
)
)
BEGIN
--Get the supllier whcovere uncovered items at the lowest cost
INSERT INTO @GreedySetCover
SELECT sort.Location_Id
,sort.Supplier_Id
,@Ranking
FROM (
SELECT uncovered.Location_Id
,si.Supplier_Id
,ROW_NUMBER() OVER
(PARTITION BY uncovered.Location_Id
ORDER BY
--This is the weighting function
SUM(uncovered.Quantity*si.Price) + --The cost of the Items
(SELECT ISNULL(si2.AmountPerOrder,0) --Plus the delivery fee
FROM SupplierItems si2
WHERE si.Supplier_Id=si2.Supplier_Id
AND
si2.Item_Id=0)
/cast(COUNT(*) as float)) RowNum --Divided by the number of items covered
FROM (
SELECT pr.Location_Id
,pr.Item_Id
,pr.Quantity
FROM PartsRequests pr
--Remove uncovered items
EXCEPT
(
SELECT gsc.Location_Id
,pr1.Item_Id
,pr1.Quantity
FROM @GreedySetCover gsc
INNER JOIN
SupplierItems si ON gsc.Supplier_Id=si.Supplier_Id
INNER JOIN
PartsRequests pr1 ON pr1.Item_Id=si.Item_Id AND pr1.Location_Id=gsc.Location_Id
WHERE pr1.ORDER_ID IS NULL
)
) uncovered
INNER JOIN
SupplierItems si ON si.Item_Id=uncovered.Item_Id
GROUP BY Location_Id, Supplier_Id
) sort
WHERE RowNum = 1
SET @Ranking=@Ranking+1
END
--SELECT *
--FROM @GreedySetCover
--ORDER BY Ranking
-- ,Location_Id
SELECT Location_Id
,Supplier_Id
,Number
,Quantity
,Price
FROM (
SELECT pr.Id PartsRequest_Id
,pr.Item_Id
,pr.Quantity
,pr.RequestTime
,pr.Location_Id
,pr.Person_Id
,pr.Order_Id
,si.Supplier_Id
,si.Number
,si.Price
,si.AmountPerOrder
,ROW_NUMBER() OVER (PARTITION BY gsc.Location_Id,pr.Item_Id
ORDER BY si.Price) RowNum
FROM @GreedySetCover gsc
INNER JOIN
PartsRequests pr ON gsc.Location_Id=pr.Location_Id
INNER JOIN
SupplierItems si ON gsc.Supplier_Id=si.Supplier_Id
AND
pr.Item_Id=si.Item_Id
) Results
WHERE RowNum=1
UNION
SELECT gsc.Location_Id
,si.Supplier_Id
,si.Number
,si.AmountPerOrder
,Price
FROM @GreedySetCover gsc
INNER JOIN
SupplierItems si ON gsc.Supplier_Id=si.Supplier_Id
WHERE AmountPerOrder>0
ORDER BY Location_Id
,Supplier_Id
,Number
这篇关于满足使用 mysql 所需的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
