sql查询做特定的需求匹配 [英] sql query to do specific matching of demands

问题描述

所以在使用SQL和PHP时遇到一个小问题。我有一个sql db，其中有4个表，即客户，请求，产品和卖方。现在，每个表都包含自我解释数据。例如客户表列出了客户，ID，名称，地址，电子邮件等，产品表具有产品ID和名称，请求表显示具有请求ID [pk的客户提出的产品请求。 ]，custID [客户表中的fk]，productid [产品表中的fk]，quantites_requested，price_requested 。卖方表包含以下数据，例如 sellersid [pk]，customerid [fk from customer table]，productsid [fk in products table]，quantites_advertised，price_advertised

现在，我想做的是，某个客户要求productid = 1（巧克力），客户id = 1，要求10kg的价格为£10.00，因此，此数据存储在数据库中。
现在，可能会有各种各样的巧克力销售商，而我希望做的就是以最便宜的方式满足客户的订单，即10公斤巧克力。合并卖家或选择最便宜的卖家。因此，例如，这是卖方表。注意：卖方表的结构如下： sellerid [pk]，customerid [fk]，productid [fk]，quantity_advertised，price_advertised 。因此，这里有一些卖家示例。

so I have a small problem whilst working with SQL and PHP. I have a sql db which has 4 tables namely, customers, requests, products and sellers. Now, each table contains self explanatory data. Such as customers tables lists the customers, ids, names, adresses, emails etc., products table has product id and the name, the requests table shows the request of products made by customers with the request id[pk], custID[fk from customer table], productid[fk in products table], quantites_requested, price_requested. The sellers table contains data such as sellersid[pk], customerid[fk from customer table], productsid[fk in products table], quantites_advertised, price_advertised
Now what I wish to do, is say a customer has requested productid= 1(chocolate),customer id=1, 10kg has been requested for £10.00 So, this data is stored in the DB. Now, there might be various sellers of chocolate and what I wish to do is fulfil the customers order i.e. 10kg of chocolates in the cheapest way possible. either by combining sellers or selecting one cheapest seller. So, for example, this is the sellers table. NOTE: the sellers table is structured by following: sellerid[pk],customerid[fk],productid[fk],quantity_advertised, price_advertised. so here are some example sellers...

1,2,1,4.00,2.00 -- This means customer id 2 is selling chocolates, 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

现在我们有要求说明

1,1,1,10.00,5.00 -- i.e. customer1, wants chocolates of 10kg for £5.00...

现在我想完成10kg的订单。因此，我需要为客户选择最便宜的价格。这可以通过选择卖家2,3和4的公斤5,4和1kg = 10kg来完成，这将总共比卖方5便宜£2.00 +£2.50 +£1.00 =£5.50，可以为6.00提供10KG 。我相信我将能够使用SQL查询执行类似的操作，在该查询中它首先将客户请求的产品ID与卖方的产品ID相匹配，即 SELECT c.Name，p.Name，s.quantity，s .price WHERE c.id = s.customerid AND p.id = s.productid AND s.quantity WHERE r.productid = s.productid来自请求r，卖方s
但是如何我能否选择最便宜的选择供应商（合并或单个大型供应商）来获得最具成本效益？我相信我需要在查询中的某处使用MIN吗？

Now I wish to fulfil the customers 10kg order. So what I need to do is select the cheapest price for the customer. This could be done by selecting sellers 2,3 and 4 with the kg's of 5,4 and 1kg=10kg which would cost a total of £2.00+£2.50+£1.00=£5.50 cheaper then seller 5 which can supply 10KG for 6.00. I believe I would be able to do something like this using an SQL query where it first matches the customer requests product id to the sellers product id i.e. SELECT c.Name, p.Name, s.quantity,s.price WHERE c.id=s.customerid AND p.id=s.productid AND s.quantity WHERE r.productid=s.productid FROM requests r, sellers s however, how would I be able to select the cheapest option supplier either combined or a single large supplier to take to be the most cost effective? I believe I would need to use the MIN somewhere in the query?

有人可以指导我如何如所述构造查询。即需要以最便宜的方式满足客户的全部要求。

Can someone guide me on how to structure a query as stated. i.e. the full customer request needs to be met in the cheapest way possible.

推荐答案

使用您当前的数据库设计（我仍然建议更改），您的查询将如下所示：

With your current database design, which I still suggest changing, your query would be something like this:

select s.name SellerName
, p.name ProductName
, s.price_advertised price

from sellers s join products p on s.productid = p.productid
join request r on r.productid = p.productid
join (
select sellerid sid
, min(price_advertised) LowestPrice
from sellers ss join request rr on ss.productid = rr.productid
group by sellerid
) sq on sid = s.sellerid and s.advertised_price = LowestPrice

这是一个简单的示例。您必须对其进行修改，以将请求与多个产品合并。

This is a simple example. You'll have to modify it to incorporate requests with more than one product.

这篇关于sql查询做特定的需求匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！