加入僅匹配特定記錄的另一個表-有解無憂

我有一個埠表：

drop table if exists ports;
create table ports(id int, name char(20));

insert into ports (id, name ) values
  (1, 'Port hedland'), 
  (2, 'Kwinana');

以及與這些港口相關的關稅表：

drop table if exists tariffs;
create table tariffs(id int, portId int, price decimal(12,2), expiry bigint(11));

insert into tariffs (id, portId, price, expiry ) values
    (1, 2, 11.00, 1648408400), 
    (2, 2, 12.00, 1648508400), 
    (3, 2, 13.00, 1648594800), 
    (4, 2, 14.00, 1651273200),
    (5, 2, 15.00, 2250000000 );

insert into tariffs (id, portId, price, expiry ) values
    (1, 1, 21.00, 1648408400), 
    (2, 1, 22.00, 1648508400), 
    (3, 1, 23.00, 1648594800), 
    (4, 1, 24.00, 1651273200),
    (5, 1, 25.00, 2250000000 );

每個關稅都有期限。

我可以很容易地進行查詢，以找出每個港口特定日期的正確關稅。例如在時間戳1648594700正確的關稅是：

SELECT * FROM tariffs 
WHERE 1648594700 < expiry AND portId = 2
ORDER BY expiry
LIMIT 1

結果：

id  portId  price   expiry
3   2   13.00   1648594800

但是，在我的應用程式中，我希望能夠從ports記錄中提取正確的關稅。

對于一個記錄，我可以這樣做：

SELECT * FROM ports 
LEFT JOIN tariffs on tariffs.portId = ports.id 
WHERE 1648594700 < tariffs.expiry AND ports.id = 2
LIMIT 1

結果：

id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800

這感覺有點“臟”，特別是因為我正在查找記錄，然后使用 LIMIT 只強制一個結果。但是沒問題。

我不能做，也不知道該怎么做，是一個將回傳埠串列的查詢，每個埠都有一個price與上述約束匹配的欄位（即，與每個埠相比最高的expiry記錄1648594700港口）。

這顯然行不通：

SELECT * FROM ports 
left join tariffs on tariffs.portId = ports.id
where 1648594700 < tariffs.expiry

由于查詢的結果，使用 timestamp 進行測驗1648594700，將是：

id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800
2   Kwinana 4   2   14.00   1651273200
2   Kwinana 5   2   15.00   2250000000
1   Port he 3   1   23.00   1648594800
1   Port he 4   1   24.00   1651273200
1   Port he 5   1   25.00   2250000000

相反，所有埠的結果（在進一步過濾之前）應該是：

id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800
1   Port he 3   1   23.00   1648594800

有沒有一種干凈、簡潔的方法來獲得這樣的結果？作為一個附加的約束，這是否可以在一個查詢中完成，沒有臨時表等？

uj5u.com熱心網友回復：

您可以選擇最低到期時間，進行加入并僅獲取具有此最低到期時間的行：

SELECT p.id, p.name, t.id, t.portId, t.price, t.expiry
FROM ports p 
LEFT JOIN tariffs t ON p.id = t.portId
WHERE expiry = (SELECT MIN(expiry) FROM tariffs WHERE 1648594700 < expiry)
ORDER BY p.id;

這會得到你想要的結果，請看這里：db<>fiddle

uj5u.com熱心網友回復：

在 MySQL 8 上，ROW_NUMBER應該在這里作業：

WITH cte AS (
    SELECT p.id, p.name, t.price, t.expiry,
           ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY t.expiry) rn
    FROM ports p
    LEFT JOIN tariffs t ON t.portId = p.id
    WHERE t.expiry > 1648594700
)

SELECT id, name, price, expiry
FROM cte
WHERE rn = 1
ORDER BY id;

該邏輯將為每個具有最近到期日的埠回傳一條記錄。

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/474682.html

標籤：mysql 加入

上一篇：連接不同資料框中的值

下一篇：查詢資料透視表以回傳參考行的串列