如何获得总体成本最低的SQL查询 [英] How can I get overall lowest cost SQL query

问题描述

尊敬的专家，

我在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，然后问一个关于您遇到的问题的更具体的问题.如果您甚至都不知道从哪里开始，则应该重新搜索分组依据 [

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

这篇关于如何获得总体成本最低的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！