从联接表中选择最高值

Question

我需要根据SQL Server中联接表的值选择一个不同的行。

表订单行：

| order_id   |  product_id|
|------------|------------|
|       1234 |         11 |
|       1234 |         22 |
|       1234 |         33 |
|       1234 |         44 |
|       1234 |         55 |
|       2222 |         66 |
|       2222 |         77 |

表产品：

| product_id |  deliverytime|
|------------|--------------|
|         11 |            2 |
|         22 |            3 |
|         33 |            5 |
|         44 |            2 |
|         55 |            1 |
|         66 |            4 |
|         77 |            1 |

我正在寻找的结果：

| order_id   |  product_id|  deliverytime|
|------------|------------|--------------|
|       1234 |         33 |            5 |
|       2222 |         66 |            4 |

提前感谢

Answer 1

我们可以在CTE中通过deliverytime DESC进行RANK，然后只取RANK 1，这是最高值。

WITH CTE AS
(SELECT 
o.product_id,
o.order_id
p.deliverytime,
RANK() OVER (PARTITION BY order_id 
ORDER BY deliverytime DESC) rn 
FROM Orderline o
JOIN Products p 
ON o.product_id = p.product_id )

SELECT
order_id,
product_id,
deliverytime
FROM CTE
WHERE rn = 1;
ORDER BY order_id

Answer 2

也许它应该适用于您，但如果有两个或多个产品具有相同的highest值，则每个订单将获得超过1行：

select v.order_id
, p2.product_id
, p2.deliverytime
from (
select o.order_id
, max(p.deliverytime) as max_deliverytime
from Orderlines o
join Products p
on o.product_id = p.product_id 
group by o.order_id
) v
join Products p2
on v.max_deliverytime = p2.deliverytime;

Answer 3

最好使用row_number来获得最高delivery_time行。如果有超过1个最高交货时间，我们也可以根据最高product_id订购

SELECT ol.order_id,
ol.product_id,
p.deliverytime
FROM (   
SELECT ol.order_id,
ol.product_id,
p.deliverytime,
row_number() over(partition by ol.order_id 
order by p.deliverytime desc, ol.product_id desc) rn 
FROM orderline ol 
JOIN products p 
ON ol.product_id = p.product_id 
)RPR
WHERE rn = 1