在沒有特定於供應商的 sql 語法的情況下選擇不同列值上的行
我試圖在沒有任何供應商特定語法的情況下選擇不同的行。我的表格佈局如下:
+--------------------------------------+---------+--------------------------+----------------------------+------------+---------------+---------------+-------------+-----------+--------------------------------------+------------+---------+ | id | version | jobAsJson | jobSignature | state | createdAt | updatedAt | scheduledAt | serverTag | mutex | awaitingOn | isBatch | +--------------------------------------+---------+--------------------------+----------------------------+------------+---------------+---------------+-------------+-----------+--------------------------------------+------------+---------+ | 6b56ef47-46e2-4905-9b4e-d8923306f98d | 0 | {some json value I want} | System.out.println(String) | PROCESSING | 1594385831872 | 1594385831872 | null | DEFAULT | resource-a | null | 0 | | c0514d03-5bd1-494f-9978-dfbc24f06d67 | 0 | {some json value I want} | System.out.println(String) | ENQUEUED | 1594385832874 | 1594385832874 | null | DEFAULT | resource-a | null | 0 | | 6b33ef73-8aa7-4d83-a171-b30da6a95c5f | 0 | {some json value I want} | System.out.println(String) | ENQUEUED | 1594385833874 | 1594385833874 | null | DEFAULT | resource-a | null | 0 | | d4d75118-57cc-476c-a86d-a6b73fc30d37 | 0 | {some json value I want} | System.out.println(String) | ENQUEUED | 1594385834874 | 1594385834874 | null | DEFAULT | resource-a | null | 0 | | 2cfbf072-5233-496d-9236-28e66cddf054 | 0 | {some json value I want} | System.out.println(String) | ENQUEUED | 1594385835874 | 1594385835874 | null | DEFAULT | resource-b | null | 0 | | d8f2d419-f394-4d4b-b375-4f1611b28170 | 0 | {some json value I want} | System.out.println(String) | ENQUEUED | 1594385836875 | 1594385836875 | null | DEFAULT | resource-b | null | 0 | | 3cdf7878-0716-4928-a059-2a74b4172c74 | 0 | {some json value I want} | System.out.println(String) | ENQUEUED | 1594385837875 | 1594385837875 | null | DEFAULT | resource-c | null | 0 | | d0bfffac-9d5b-4c7e-a82b-ca0f93d1a1da | 0 | {some json value I want} | System.out.println(String) | ENQUEUED | 1594385838875 | 1594385838875 | null | DEFAULT | 79333f04-ab41-41dc-b004-8b2d74055d38 | null | 0 | | c1d568f3-f1e1-4d4d-9ca5-21129bf6d066 | 0 | {some json value I want} | System.out.println(String) | ENQUEUED | 1594385839875 | 1594385839875 | null | DEFAULT | 1b8b941c-6bdc-45cf-93b5-1856f9de404a | null | 0 | +--------------------------------------+---------+--------------------------+----------------------------+------------+---------------+---------------+-------------+-----------+--------------------------------------+------------+---------+
我正在嘗試選擇 state = ‘ENQUEUED’、servertag 位於(‘DEFAULT’)中的所有行,這些行應該按 createdAt 排序,並且這些行在欄位互斥鎖上應該是唯一的。
目標是每個互斥體只有 1 行 state = ‘PROCESSING’。STATE=‘ENQUEUED’ 的選定行將更新為 ‘PROCESSING’。在此處的範例中,預期結果是:
- 2cfbf072-5233-496d-9236-28e66cddf054 -> 第一份使用互斥鎖的作業 = resource-b
- 3cdf7878-0716-4928-a059-2a74b4172c74 -> 使用互斥鎖的第一份工作 = resource-c
- d0bfffac-9d5b-4c7e-a82b-ca0f93d1a1da -> 沒有互斥體,因此具有唯一值,使查詢更容易
- c1d568f3-f1e1-4d4d-9ca5-21129bf6d066 -> 沒有互斥體,因此具有唯一值,使查詢更容易
=> 沒有返回具有互斥資源-a 的作業,因為它已被第一個作業使用。
我現在的查詢適用於 SQLite,但不適用於其他數據庫(mySQL、oracle、DB2),如下所示:
select j.jobAsJson from (select id from jobrunr_jobs where state = 'ENQUEUED' AND servertag in ('DEFAULT') and mutex NOT IN (select mutex from jobrunr_jobs where state = 'PROCESSING') GROUP BY mutex order by createdat) r INNER JOIN jobrunr_jobs j ON r.id = j.id
我想要一個與數據庫供應商無關的查詢。
Postgres 和 SQL Server 抱怨 a
GROUP BY
被使用id
但不是它的一部分。
錯誤?
首先,我懷疑您的查詢中有一個細微的錯誤:
... AND servertag IN ('DEFAULT') AND mutex NOT IN (select mutex from jobrunr_jobs where state = 'PROCESSING') ...
NOT IN
您將結果限制為帶有 servertag ‘DEFAULT’ 的互斥體,但在子查詢中不會重複該限制。因此,您排除了具有狀態“正在處理”的行和任何伺服器標籤的互斥鎖。可能是故意的,但它聞起來像一個錯誤。即使沒有任何問題(因為互斥的互斥值集),它可能仍然對性能不利。此外,
mutex NOT IN (<subselect>)
這是一個臭名昭著的危險構造。如果mutex
可以為 NULL,則查詢將意外中斷。在 Postgres 中,通常不鼓勵這種結構。我認為大多數 RDBMS 也是如此。詢問
基本上,您需要每個互斥鎖的最新行,
state = 'ENQUEUED'
其中不state = 'PROCESSING'
存在任何行。我假設您想將這兩個條件限制為具有以下內容的行servertag = 'DEFAULT'
:SELECT jobAsJson -- careful with mixed-case spelling! FROM jobrunr_jobs AS j WHERE state = 'ENQUEUED' AND servertag = 'DEFAULT' AND NOT EXISTS ( SELECT 1 FROM jobrunr_jobs AS j2 WHERE j2.mutex = j.mutex AND j2.servertag = 'DEFAULT' -- assuming you want this? AND (j2.state = 'PROCESSING' OR j2.state = 'ENQUEUED' AND j2.createdat > j.createdat) );
EXISTS
是非常基本的標準 SQL,並且可以在每個體面的 RDBMS 中工作 - 與 CTE 和視窗函式不同,後者是後來添加的,並非所有地方都支持。也消除了對可疑的
NOT IN
.它應該很快,尤其是在索引支持的情況下。
除了 1:要“與 DB 無關”(這永遠不可能),不要使用大小寫混合的標識符,這些標識符在各種 RDBMS 中的處理方式不同。
旁白 2:假設您想要將已辨識的行標記為
'PROCESSING'
. 那麼你會想要返回的不僅僅是jobAsJson
. 或UPDATE
立即辨識行。看: