我有两个 tables 我想 select 为每个 shippingType 订购最多（数量）的产品（见结果 table）
装运 table
| id | shipmentTyp | amount | productID |
|---|---|---|---|
| 1 | A | 3 | 1 |
| 2 | S | 7 | 1 |
| 3 | A | 12 | 3 |
| 4 | T | 15 | 2 |
| 5 | T | 7 | 1 |
| 6 | T | 4 | 3 |
| 7 | A | 1 | 3 |
| 8 | S | 78 | 2 |
产品 table
| productID | productName |
|---|---|
| 1 | P1 |
| 2 | P2 |
| 3 | P3 |
结果 table
| shipmentType | productName | amount |
|---|---|---|
| A | P3 | 12 |
| S | P2 | 78 |
| T | P2 | 15 |
回答1
对我来说，这似乎是一个简单的 ROW_NUMBER() 问题 -
SELECT
SH.shipmentType, P.productName, SH.amount
FROM
(SELECT
shipmentType, amount, productID,
ROW_NUMBER() OVER (PARTITION BY shipmentType ORDER BY amount DESC) RN
FROM
shipment) SH
JOIN
Products P ON SH.productID = P.productID
WHERE
RN = 1;