У меня есть эти две таблицы:
Table: Estimates
| EstimateNumber | Quantity | TotalCost |
| -------------- | -------- | --------- |
| 183232 | 2000 | 5890.42 |
| 183232 | 2500 | 6935.63 |
| 183232 | 3500 | 9016.21 |
| 183232 | 3000 | 7980.28 |
Table: Jobs
| JobNumber | EstimateNumber | QuantityOrdered |
| --------- | -------------- | --------------- |
| PK05188 | 183232 | 2500 |
| PK05591 | 183232 | 3000 |
Я бы хотел это:
| Job Number | TotalCost | Other_Info_From_Corresponding_Estimates_Row |
| ---------- | --------- | ------------------------------------------ |
| PK05188 | 6935.63 | 'Example' |
| PK05591 | 7980.28 | 'Example' |
Где правильная сопутствующая стоимость - это стоимость с ближайшим количеством. Изначально я делал что-то вроде этого:
SELECT
Estimates.EstimateNumber,
Estimates.TotalCost
FROM Estimates
INNER JOIN
(SELECT DISTINCT
Jobs.EstimateNumber,
FIRST_VALUE( Estimates.Quantity ) OVER ( PARTITION BY Jobs.EstimateNumber ORDER BY ABS( Jobs.QuantityOrdered - Estimates.Quantity ) ASC ) CorrectQuantity
FROM Jobs
INNER JOIN Estimates
ON Jobs.EstimateNumber = Estimates.EstimateNumber ) AS QuantityTable
ON Estimates.EstimateNumber = QuantityTable.EstimateNumber AND Estimates.Quantity = QuantityTable.CorrectQuantity
Затем используйте это как подзапрос и сопоставьте JobNumber на основе соответствующего EstimateNumber. Однако мне не удалось понять, что, хотя JobNumber отличается, они могут иногда относиться к одной и той же оценке. Это вызывает по понятным причинам странное поведение, когда одно из заданий «выиграет» самое близкое совпадение в зависимости от основного порядка операций. И даже без этого поведения при присоединении он будет дублировать задания.
Какие-либо предложения?
EstimateNumber не уникален, как показано в данных таблицах. Каждая величина уникальна для данного EstimateNumber.
Для ваших выборочных данных вы можете использовать точное совпадение:
select j.*, e.*
from jobs j left join
estimates e
on j.jobnumber = e.jobnumber and
j.QuantityOrdered = e.quantity;
Однако ваш вопрос предполагает, что точное совпадение может не совпадать. Вы не указываете правила, определяющие «ближайший», но вы можете использовать для этого боковое соединение:
select j.*, e.*
from jobs j outer apply
(select top (1) e.*
from estimates e
where j.jobnumber = e.jobnumber and
order by abs(j.QuantityOrdered - e.quantity)
) e
Предполагая, что у вас не всегда может быть точное совпадение, из вашей формулировки, следующее должно дать вам образец вывода:
select j.JobNumber, e.*
from Jobs j
outer apply (
select totalCost, OtherStuff from (
select e.totalcost, e.OtherStuff, Row_Number() over(order by Abs(j.quantityordered-e.quantity)) rn
from Estimates e
where e.estimatenumber=j.estimatenumber
)e where rn=1
)e
Попробуй это:
;with cte as(
SELECT
A.JobNumber,
A.EstimateNumber,
B.TotalCost,
estimate_rank=row_number() over
(partition by A.JobNumber,A.EstimateNumber
order by A.JobNumber,
A.EstimateNumber,ABS(A.QuantityOrdered-B.Quantity)
)
FROM Jobs A
JOIN Estimates B ON A.EstimateNumber=B.EstimateNumber
)
SELECT JobNumber,TotalCost
FROM cte
WHERE
estimate_rank=1
Хорошие ответы должны включать объяснение в дополнение к коду, чтобы ОП мог учиться на нем.
Всегда ли в таблице «Оценки» EstimateNumber и Quantity уникальны?