來自 2 個表的 ORDER 以實現 API 返回的特定訂單

有一個基於“圖像 ID”和“圖像版本”（同一張圖像可以有多個版本）返回圖像相似性的外部 API。相似度 ORDER 由 API 給出。

API 返回如下內容：

+---------+---------+
|   id    | version |
+---------+---------+
|  37967  |    2    |
|    236  |    1    |
|  37967  |    1    |
|   1413  |    2    |
+---------+---------+ 

然後我需要檢索 MySQL 數據庫（包含 2 個表）中的條目，並保持與 API 返回的 ORDER 相同的 ORDER。

這就是我遇到問題的地方。下面是兩張表：

"img" MySQL table:
+---------+-----------------------+
|   id    | lots of other columns |
+---------+-----------------------+
|    236  |         data          |
|   1413  |         data          |
|  37967  |         data          |
+---------+-----------------------+

"vers" MySQL table:
+---------+---------+
|   id    | version |
+---------+---------+
|    236  |    1    |
|   1413  |    1    |
|   1413  |    2    |
|  37967  |    1    |
|  37967  |    2    |
|  37967  |    3    |
+---------+---------+ 

我能得到的最接近的結果是使用 ORDER BY FIELD，但它仍然與 API 返回的 ORDER 不同。

My query:

SELECT i.id, v.version
FROM img i
LEFT JOIN vers v ON i.id=v.id 
WHERE ((i.id=37967 AND v.version=2) 
OR (i.id=236 AND v.version=1) 
OR (i.id=37967 AND v.version=1) 
OR (i.id=1413 AND v.version=2)) 
ORDER BY FIELD(i.id, 37967,236,37967,1413), FIELD(v.version,2,1,1,2) 

Results:
+---------+---------+
|   id    | version |
+---------+---------+
|  37967  |    2    |
|  37967  |    1    |
|    236  |    1    |
|   1413  |    2    |
+---------+---------+

如您所見，訂單並不完全是 API 返回的訂單 :(

任何幫助將不勝感激，在此先感謝大家。

（從聊天中複製）

MySQL 版本是 5.6.40

最大記錄數為 100。

---

將JSON字元串解析為列舉表（最多100條記錄）的方法適用於5.6.40版本。

set @JSON='{"imgResults":[{"id":"37967","version":"2"},{"id":"236","version":"1"},{"id":"37967","version":"1"},{"id":"1413","version":"2"}]}'

這是範例源 JSON 字元串。

它可以作為上述查詢的查詢參數傳輸到伺服器，該查詢將值分配給變數或作為字元串文字立即插入查詢文本。

也可以將它作為查詢參數傳輸到伺服器，用於解析它的以下查詢，即中間使用者定義變數是可選的。

---

現在我們將 JSON 解析為一個表（從 8.0 版本開始可以模擬 JSON_TABLE 函式）：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(json, '"id":"', num+2), '"id":"', -1), '"', 1) id,
      SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(json, '"version":"', num+2), '"version":"', -1), '"', 1) version,
      MIN(num) idx
FROM (SELECT @JSON json) source,            -- source JSON string
( SELECT numbers1.num*10+numbers2.num num   -- create numbers table, 0..99
 FROM ( SELECT 0 num UNION 
        SELECT 1 UNION 
        SELECT 2 UNION 
        SELECT 3 UNION 
        SELECT 4 UNION 
        SELECT 5 UNION 
        SELECT 6 UNION 
        SELECT 7 UNION 
        SELECT 8 UNION 
        SELECT 9 ) numbers1,
      ( SELECT 0 num UNION
        SELECT 1 UNION
        SELECT 2 UNION 
        SELECT 3 UNION
        SELECT 4 UNION
        SELECT 5 UNION 
        SELECT 6 UNION 
        SELECT 7 UNION 
        SELECT 8 UNION 
        SELECT 9 ) numbers2 ) numbers
GROUP BY id,version
-- ORDER BY idx;

輸出：

編號 | 版本 | 編號
:---- | :------ | --:
37967 | 2 | 0
236 | 1 | 1
37967 | 1 | 2
1413 | 2 | 3

---

您可以將此解析器作為源數據子查詢加入到您的查詢中，並使用其idx欄位以與源 JSON 中記錄相同的順序對最終結果進行排序。

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