选择最便宜的价格，然后按供应商进行分组

Question

我想为技术采购团队提供默认供应商设置，以便让他们知道在哪里订购零件。这是基于名为Price_book的表。为此，我需要一张新的桌子，其中包括党派，最便宜的价格和供应商代码来通过系统处理。我如何仅选择提供最便宜价格的供应商？

我正在这样做，以简化技术固定团队的订购过程。我走得太远，以至于我只有每个供应商最便宜的价格(关于最低订单数量(。我缺少的内容是只选择最便宜价格的供应商的部分。因此，与相应供应商的最便宜的价格。

查询很短：

SELECT DISTINCT pb.partno, MIN(pb.purch_price * c.rate) AS 'cheapest_price', pb.supplier
FROM price_book pb
LEFT JOIN currency c ON c.currency = pb.currency
WHERE pb.contract_id <> 0
AND pb.expire_date > Datediff(Day, '31 Dec 1971', Getdate())
AND pb.order_type = 'P'
AND pb.condition = 'N'
GROUP BY partno, supplier

其中的子句与内部流程有关。

结果应该是列的列，价格，价格和相应的供应商。每个党派只有最便宜的供应商。

Answer 1

尝试TOP(1) WITH TIES

SELECT TOP(1) WITH TIES pb.partno, pb.purch_price * c.rate [cheapest_price], pb.supplier
FROM price_book pb
LEFT JOIN currency c ON c.currency = pb.currency
WHERE pb.contract_id <> 0
AND pb.expire_date > Datediff(Day, '31 Dec 1971', Getdate())
AND pb.order_type = 'P'
AND pb.condition = 'N'
ORDER BY row_number() over(partition by partno order by pb.purch_price * c.rate [cheapest_price] desc);

Answer 2

您可以为此目的尝试Windows功能，代码将类似于以下示例

注意 - 代码未测试。

SELECT * from
(SELECT pb.partno,pb.supplier,pb.purch_price * c.rate as price, 
row_number() over(partition by pb.partno,pb.supplier order by pb.purch_price * c.rate )rn
FROM price_book pb
LEFT JOIN currency c ON c.currency = pb.currency
WHERE pb.contract_id <> 0
AND pb.expire_date > Datediff(Day, '31 Dec 1971', Getdate())
AND pb.order_type = 'P'
AND pb.condition = 'N')a
where a.rn=1

Answer 3

有一个查看：

DECLARE @priceBook TABLE (PriceID       INT IDENTITY(1, 1)
,                         PartNo        NVARCHAR(10)
,                         Price         DECIMAL(18, 2)
,                         fk_SupplierID INT);
INSERT INTO @priceBook (PartNo, Price, fk_SupplierID)
VALUES (N'123', 8.99, 1)
,      (N'123', 9.99, 2)
,      (N'456', 10.99, 1)
,      (N'456', 3.99, 2)
,      (N'456', 12.99, 3);
WITH cte AS
    (SELECT PriceID
     ,      PartNo
     ,      Price
     ,      fk_SupplierID
     ,      ROW_NUMBER() OVER (PARTITION BY PartNo
ORDER BY Price ASC) RN
       FROM @priceBook)
SELECT  cte.PriceID
,       cte.PartNo
,       cte.Price
,       cte.fk_SupplierID
  FROM  cte
 WHERE  rn = 1;

这是上述窗口功能的基本示例。这确实在MS SQL Server上运行，不确定MySQL。对其进行测试，如果对您有效，则可以调整确切的结构。

Answer 4

我现在找到了一个对我有用的代码：

SELECT B.partno,
MAX(C.supplier) AS supplier
FROM
(SELECT A.partno,
MIN(A.price) AS price
FROM
(SELECT DISTINCT pb.partno AS partno, MIN(pb.purch_price * c.rate) AS price, pb.supplier AS supplier
FROM price_book pb
LEFT JOIN currency c ON c.currency = pb.currency
LEFT JOIN part p ON p.partno = pb.partno
WHERE pb.contract_id <> 0
AND pb.expire_date > Datediff(Day, '31 Dec 1971', Getdate())
AND pb.order_type = 'P'
AND pb.condition = 'N'
AND p.mat_class IN ('C', 'E')
GROUP BY pb.partno, supplier) A
GROUP BY A.partno) B
JOIN (SELECT DISTINCT pb.partno AS partno, MIN(pb.purch_price * c.rate) AS price, pb.supplier AS supplier
FROM price_book pb
LEFT JOIN currency c ON c.currency = pb.currency
LEFT JOIN part p ON p.partno = pb.partno
WHERE pb.contract_id <> 0
AND pb.expire_date > Datediff(Day, '31 Dec 1971', Getdate())
AND pb.order_type = 'P'
AND pb.condition = 'N'
AND p.mat_class IN ('E', 'C')
GROUP BY pb.partno, supplier) C ON B.partno=C.partno and B.price=C.price
GROUP BY B.partno

非常感谢您给我的支持！

欢呼，Dominic