在沒有特定於供應商的 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 抱怨 aGROUP 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立即辨識行。看：

• Postgres 更新…限制 1

引用自：https://dba.stackexchange.com/questions/270740