如何获得总体成本最低的SQL查询

本文介绍了如何获得总体成本最低的SQL查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

尊敬的专家， 我在Microsoft Access/SQL查询中遇到问题. 我有三个数据库表(1. Material，2.Suppliers，3.OFFERS)OFFERS 表包含不同材料的供应商的不同报价，例如:

Dear Experts, I have problem in Microsoft Access/SQL query. I have three database tables (1. Material, 2. Suppliers, 3. OFFERS) OFFERS table contains different offers of suppliers for different materials like as:

OfferID Quantity UnitPrice DelTime Supplier Material 1 1 99 5 A Chair 2 3 90 5 A Chair 3 5 80 5 A Chair 4 1 95 5 B Chair 5 1 90 5 C Chair 6 3 85 5 C Chair 7 10 80 5 C Chair

我想获得整体成本最低的14张椅子的供应商. 整体最低价格的文书工作如下: 材质:椅子 订单数量:14 供应商A的成本: 10 x 80 = 800(按报价3) 3 x 90 = 270(按报价2) 1 x 99 = 99(按报价1) 供应商A的总成本为:1169 供应商B的费用: 14 x 95 = 1330(按要约4) 供应商B的总成本为:1330 供应商C的成本: 10 x 80 = 800(按报价7) 3 x 85 = 255(按报价6) 1 x 90 = 90(优惠5) 供应商C的总成本为:1145 在这种情况下，供应商C提供了14个椅子的最低成本. 我如何通过查询访问它?

I want to get the supplier with overall lowest cost for 14 chairs. The paper work for overall lowest price is given as: Material: Chairs Order quantity: 14 Cost for Supplier A: 10 x 80 = 800 (as by offer 3) 3 x 90 = 270 (as by offer 2) 1 x 99 = 99 (by offer 1) Overall Cost for supplier A is: 1169 Cost for Supplier B: 14 x 95 = 1330 (by offer 4) Overall Cost for supplier B is: 1330 Cost for Supplier C: 10 x 80 = 800 (by offer 7) 3 x 85 = 255 (by offer 6) 1 x 90 = 90 (offer 5) Overall Cost for supplier C is: 1145 In this, Supplier C offers the minimum cost for 14 chairs. How I can access it via a query?

推荐答案

我怀疑您将需要编写一些代码来执行此操作，因为SQL计算每个值，然后算出哪一个便宜些，这将是非常复杂的IMO.我不是SQL专家，您可能会找到可以提供简单SQL解决方案的人，但对我而言似乎并非如此.

I suspect you''re going to need to write some code to do this, because the SQL to calculate it for each and then work out which is cheaper will be quite complex IMO. I am no SQL guru, you may find someone who can give a simple SQL solution, but it doesn''t seem that way to me.

为什么不开始编写SQL，然后问一个关于您遇到的问题的更具体的问题.如果您甚至都不知道从哪里开始，则应该重新搜索 SQL [ ^ ]. 如果您对SQL有所了解，则可以先研究分组依据 [ ^ ]，因为听起来您将需要按供应商分组. 希望这对您有帮助. Why don''t you start writing the SQL and then ask a more specific question about the problems you are having. If you don''t even know where to start, you should reasearch SQL[^] first. If you have some knowledge in SQL, you could start by researching the Group By[^], because it sounds like you are going to need to Group By your supplier. Hope this helps.

返回子查询结果的最小值，该子查询按供应商分组并取数量*单价的总和.不确定Access，但是MSSQL版本看起来像: Return the min of the result of the the subquery that groups by suppliers and takes the SUM of Quantity * UnitPrice. Not sure about Access, but the MSSQL version would look something like: SELECT TOP 1 Total, TotalUnits, Supplier FROM ( SELECT SUM(Quantity * UnitPrice) AS Total, SUM(Quantity) AS TotalUnits, Supplier FROM Offers GROUP BY Supplier ) AS Totals ORDER BY Total ASC