我有 3 個桌子盒子、石頭和紙；每個盒子都通過一塊石頭與一篇論文相關，所以我的目標是為每篇論文獲取最后一個盒子（盒子可以共享論文）。已嘗試使用 ActiveRecord 方式進入 rails 但無法在 uuid 庫中使用聚合函式，因此這不起作用：
Box.joins(:stone).group('stone.paper_id').maximum(:id).values
由于缺少石頭表，我正在努力尋找純 SQL 陳述句，我有以下內容：
select distinct on (papers.id) boxes.created_at, boxes.id
from papers
left join boxes on paper.id = boxes.id
order by paper.id, boxes.created_at DESC;
所有 3 個表的主鍵都是 uuid 以及與查詢無關的其他列，因為我只想回傳每篇論文最后一個框的 uuid。
table:boxes
-------------------------------------- ----------------- ----------- --------------------------------- --------------------------------------
| id | email | delivered | created_at | stone_id |
-------------------------------------- ----------------- ----------- --------------------------------- --------------------------------------
| 61a341b0-a147-4534-9368-fdbc7b61fc0c | test@test.com | true | Fri, 04 Mar 2022 00:19:31 0000 | 7fda6668-e9b2-45b3-957a-fbdbcd833cd0 |
| c20f4b61-8606-4aa7-870a-29b9df9d9492 | test2@test.com | true | Thu, 24 Feb 2022 11:42:01 0000 | cdb35b8a-b553-4095-8b14-e855ebdf5044 |
| 9202384f-1895-4f94-9972-3ef837655aae | test3@test.com | false | Thu, 10 Mar 2022 00:59:54 0000 | bbd5dcbc-b38d-4751-aaac-2b3dd83c5545 |
-------------------------------------- ----------------- ----------- --------------------------------- --------------------------------------
table:stones
-------------------------------------- -------- ------ --------------------------------- --------------------------------------
| id | status | code | created_at | paper_id |
-------------------------------------- -------- ------ --------------------------------- --------------------------------------
| 7fda6668-e9b2-45b3-957a-fbdbcd833cd0 | 1 | 3 | Sun, 06 Mar 2022 12:58:56 0000 | a0acba15-e321-4f9f-996f-a6c16e56300d |
| cdb35b8a-b553-4095-8b14-e855ebdf5044 | 1 | 4 | Thu, 03 Mar 2022 19:57:14 0000 | a0acba15-e321-4f9f-996f-a6c16e56300d |
| bbd5dcbc-b38d-4751-aaac-2b3dd83c5545 | 2 | 5 | Fri, 11 Mar 2022 11:50:08 0000 | de936cf2-c158-4961-9ef4-60affc4ff87f |
-------------------------------------- -------- ------ --------------------------------- --------------------------------------
table:papers
-------------------------------------- ------ ---------------------------------
| id | type | created_at |
-------------------------------------- ------ ---------------------------------
| a0acba15-e321-4f9f-996f-a6c16e56300d | 1 | Sat, 05 Mar 2022 05:59:00 0000 |
| de936cf2-c158-4961-9ef4-60affc4ff87f | 5 | Thu, 03 Mar 2022 19:57:14 0000 |
| 473a9dd4-3f38-49d0-8d1e-b5ab87e8ea92 | 4 | Sat, 12 Mar 2022 22:55:16 0000 |
-------------------------------------- ------ ---------------------------------
box1 has a stone1 that relates to paper1
box2 has a stone2 that relates to paper1
box3 has a stone3 that relates to paper2
as a result i want box1 and box3 uuids since they are the most recent boxes (order by the time they were created) for each available paper, box2 is ignored since shares paper1 with box1 and it is not the most recent one. any help will be much appreciated! thanks in advance!
result:most recent boxes ids
--------------------------------------
| id |
--------------------------------------
| 61a341b0-a147-4534-9368-fdbc7b61fc0c |
| 9202384f-1895-4f94-9972-3ef837655aae |
--------------------------------------
uj5u.com熱心網友回復：
你可以做這個查詢（結果在這里）
with x as (
select row_number() over (partition by p.id order by b.created_at desc) as rn,b.id as id_box,p.id as id_paper
from boxes b join stones s on b.stone_id = s.id
join papers p on p.id = s.paper_id)
select x.id_box from x where rn = 1
uj5u.com熱心網友回復：
基于wich 的cte最新發現與table 相關。然后加入表。idpaper_idpapersbox
嘗試讓我知道它是否有幫助：
with cte as
(
select s.id,
s.paper_id,row_number() over(partition by paper_id order by created_at desc ) as rn
from stones s
) select b.id
from boxes b
inner join cte c on c.id=b.stone_id
inner join papers p on c.paper_id=p.id
where c.rn=1
order by b.created_at desc;
結果：
id 9202384f-1895-4f94-9972-3ef837655aae 61a341b0-a147-4534-9368-fdbc7b61fc0c
演示
uj5u.com熱心網友回復：
“基于表關系獲取最后一條記錄的 SQL 查詢”
表中沒有記錄，只是按任何順序分組到頁面中的邏輯行。沒有默認或特定順序，例如將電子表格放入表格頁面，并且將行作為堆放入頁面中。您必須清楚，您永遠無法找到最后的“記錄”！
在資料庫中，您會發現只有您將存盤的內容。如果要查找插入的最后一行，則需要在 INSERT 陳述句中提供此資訊。
我說你，因為任何其他用戶都可以同時執行相同的插入，使用相同的值......這就是并發！
現在的問題是“你想要什么？ ”
uj5u.com熱心網友回復：
嘗試這個 ：
SELECT DISTINCT ON (p.id)
b.id
FROM boxes AS b
INNER JOIN stones AS s
ON s.id = b.stone_id
INNER JOIN papers AS p
ON p.id = s.paper_id
ORDER BY p.id, b.created_at DESC
在dbfiddle中查看測驗結果。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/444785.html
標籤：sql ruby-on-rails postgresql rails-activerecord postgresql-13