架構詳細資訊
我們正在一個專案中維護集合資料。Collections 表的主要列是id(INT), collectionId(String UUID), versionNo(INT), status(PUBLISHED/NEW/PURCHASED/DELETED/ARCHIVED)。每個集合可以有多個版本。對于每個不同的版本，versionNo, id,status列將具有不同的值但collectionId將是相同的。
樣本資料
id collectionId versionNo status
5 17af2c88-888d-4d9a-b7f0-dfcbac376434 1 PUBLISHED
80 17af2c88-888d-4d9a-b7f0-dfcbac376434 2 PUBLISHED
109 17af2c88-888d-4d9a-b7f0-dfcbac376434 3 NEW
6 d8451652-6b9e-426b-b883-dc8a96ec0010 1 PUBLISHED
問題陳述
我們想要獲取最高發布版本的集合的詳細資訊。例如：對于上述資料集，所需的輸出是
id collectionId versionNo status
80 17af2c88-888d-4d9a-b7f0-dfcbac376434 2 PUBLISHED
6 d8451652-6b9e-426b-b883-dc8a96ec0010 1 PUBLISHED
我們嘗試了以下查詢，但要么獲取重復條目，要么僅獲取單個版本的集合：
select * from Collections where status="PUBLISHED" group by collectionId having versionNo=max(versionNo);select T1.* from Collections T1 inner join Collections T2 on T1.collectionId = T2.collectionId AND T1.id <> T2.id where T1.status="PUBLISHED" AND T1.versionNo > T2.versionNo;
更新：我使用的是 MYSQL 版本 5.7.12。
uj5u.com熱心網友回復：
為了獲得最高版本，您必須使用連接將集合資料與其自身進行比較，如下所示
select C1.* from Collections C1
left join Collections C2 on C1.collectionId = C2.collectionId
AND C1.status="PUBLISHED" AND C2.status="PUBLISHED"
AND C1.versionNo < C2.versionNo
WHERE C2.versionNo IS NULL
AND C1.status="PUBLISHED"
uj5u.com熱心網友回復：
ROW_NUMBER()假設您正在運行 MySQL 8 ：
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY collectionId ORDER BY versionNo DESC) rn
FROM Collections
WHERE status = 'PUBLISHED'
)
SELECT id, collectionId, versionNo, status
FROM cte
WHERE rn = 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/529140.html