SQL показывает самого дешевого поставщика

Я создаю запрос, чтобы найти самого дешевого поставщика для определенных продуктов.

Пользователь сможет выбрать до 4 поставщиков для сравнения.

Мой полный код

declare @Sup as varchar(20) set @Sup = 'WESTF'
declare @Sup2 as varchar(20) set @Sup2 = 'NAVIG'
declare @Sup3 as varchar(20) set @Sup3 = 'PRIMF'
declare @Sup4 as varchar(20) set @Sup4 = ''

select 'Product' as ProductCode, 'Description' as Description, 
@Sup as Cost, @Sup as SupplierDate, 
@Sup2 as Cost2, @Sup2 as SupplierDate2,
@Sup3 as Cost3, @Sup3 as SupplierDate3,
@Sup4 as Cost4, @Sup4 as SupplierDate4, 0 as Cheapest

union all

select p.ProductCode,p.Description,
'£' + cast(psp1.Cost as varchar) + ' Per ' + Cast(psp1.Per as varchar)as Cost1,psp1.SupplierDate,
'£' + cast(psp2.Cost as varchar) + ' Per ' + Cast(psp2.Per as varchar)as Cost2,psp2.SupplierDate,
'£' + cast(psp3.Cost as varchar) + ' Per ' + Cast(psp3.Per as varchar)as Cost3,psp3.SupplierDate,
'£' + cast(psp4.Cost as varchar) + ' Per ' + Cast(psp4.Per as varchar)as Cost4,psp4.SupplierDate,


MinValue as Cheapest
from
        product as p 
        left join
        (
        select psp.productid,psp.StandardBuy as Cost ,Per.Name as Per, 
        per.ConversionToBase,s.SupplierCode,
        cast(convert(Date, psp.StandardBuyDateTime, 103) as varchar) as SupplierDate
        from ProductSupplierPrice as psp
        left join Supplier as s on s.SupplierID = psp.SupplierID
        left join Per on Per.PerID = psp.BuyPerID
        where s.SupplierCode = @Sup
        ) as psp1 on psp1.ProductID = p.ProductId

        left join
        (
        select psp.productid,psp.StandardBuy as Cost ,Per.Name as Per,  
        per.ConversionToBase,s.SupplierCode,
        cast(convert(Date, psp.StandardBuyDateTime, 103) as varchar) as SupplierDate
        from ProductSupplierPrice as psp
        left join Supplier as s on s.SupplierID = psp.SupplierID
        left join Per on Per.PerID = psp.BuyPerID
        where s.SupplierCode = @Sup2
        ) as psp2 on psp2.ProductID = p.ProductId

        left join
        (
        select psp.productid,psp.StandardBuy as Cost ,Per.Name as Per,  
        per.ConversionToBase,s.SupplierCode,
        cast(convert(Date, psp.StandardBuyDateTime, 103) as varchar) as SupplierDate
        from ProductSupplierPrice as psp
        left join Supplier as s on s.SupplierID = psp.SupplierID
        left join Per on Per.PerID = psp.BuyPerID
        where s.SupplierCode = @Sup3
        ) as psp3 on psp3.ProductID = p.ProductId

        left join
        (
        select psp.productid,psp.StandardBuy as Cost ,Per.Name as Per, 
        per.ConversionToBase,s.SupplierCode,
        cast(convert(Date, psp.StandardBuyDateTime, 103) as varchar) as SupplierDate
        from ProductSupplierPrice as psp
        left join Supplier as s on s.SupplierID = psp.SupplierID
        left join Per on Per.PerID = psp.BuyPerID
        where s.SupplierCode = @Sup4
        ) as psp4 on psp4.ProductID = p.ProductId

CROSS APPLY (SELECT MIN(d) MinValue FROM (VALUES 
(psp1.Cost/psp1.ConversionToBase), 
(psp2.Cost/psp2.ConversionToBase), 
(psp3.Cost/psp3.ConversionToBase), 
(psp4.Cost/psp4.ConversionToBase)) AS a(d)) A

where p.Deleted = 0 and p.Description like '%endfeed%'

однако важный бит:

CROSS APPLY (SELECT MIN(d) MinValue FROM (VALUES 
(psp1.Cost/psp1.ConversionToBase), 
(psp2.Cost/psp2.ConversionToBase), 
(psp3.Cost/psp3.ConversionToBase), 
(psp4.Cost/psp4.ConversionToBase)) AS a(d)) A

Это находит самую дешевую цену, но я не хочу отображать самую дешевую цену, я хочу знать код поставщика, связанный с самой дешевой ценой.

Я не уверен, что это лучший способ сделать это, любая помощь будет оценена по достоинству.


sql sql-server tsql cross-apply
person Richard    schedule 16.06.2016    source источник
comment
искать row_number() над   -  person paparazzo    schedule 16.06.2016

Ответы (1)


arrow_upward
1
arrow_downward

Замените его на

CROSS APPLY (
 SELECT top(1) d,supplierId
 FROM (VALUES 
   (psp1.Cost/psp1.ConversionToBase, psp1.<supplierID here>), 
   (psp2.Cost/psp2.ConversionToBase, psp2.<supplierID here>), 
   (psp3.Cost/psp3.ConversionToBase, psp3.<supplierID here>), 
   (psp4.Cost/psp4.ConversionToBase, psp4.<supplierID here>)
   ) AS a(d,supplierId)
 ORDER BY d ASC) A
person Serg    schedule 16.06.2016
comment
думал, что сделал это тогда, но, оказывается, показывает это наоборот, показывая самое дорогое. - person Richard; 16.06.2016
comment
Да, я пробовал это, а потом все просто говорят Null, хотя не знаю почему. - person Richard; 16.06.2016
comment
NULL считается минимальным значением в ORDER BY. А что вам нужно, когда одно или несколько левых объединений не возвращают данных? Если вам нужно игнорировать NULLS, добавьте WHERE supplierId IS NOT NULL . - person Serg; 16.06.2016