我有以下 3 個PostgreSQL表：

錢包：

+----+--------+-----------+-----------+--------+
| id | name   | createdAt | updatedAt | locked |
+----+--------+-----------+-----------+--------+
| 1  | name_1 | ...       | ...       | f      |
| 2  | name_2 | ...       | ...       | f      |
| 3  | name_3 | ...       | ...       | t      |
+----+--------+-----------+-----------+--------+

命令：

+-----------------------------------+--------+-----------+-----------+
|    id    |    rate    |   status  | reason | createdAt | updatedAt |  
+-----------------------------------+------- +-----------+-----------+
| 4aface2d | 0.00062231 | accepted  |        | ...       | ...       |
| 9289add0 | 0.00062231 | new       |        | ...       | ...       |
| b3dfe546 | 0.00062231 | completed |        | ...       | ...       |
| f70ed6be | 0.00062231 | pending   |        | ...       | ...       |
+-----------------------------------+--------+-----------+-----------+

數量：

+----+-------+-----------+-----------+-----------+----------+
| id | value | createdAt | updatedAt |  orderId  | walletId |
+----+-------+-----------+-----------+-----------+----------+
| 1  | 200   | ...       | ...       | 4aface2d  | 1        |
| 2  | 100   | ...       | ...       | 9289add0  | 3        |
| 3  | 200   | ...       | ...       | b3dfe546  | 1        |
| 4  | 50    | ...       | ...       | 9289add0  | 2        |
| 5  | 100   | ...       | ...       | f70ed6be  | 3        |
| 6  | 200   | ...       | ...       | f70ed6be  | 2        |
+----+-------+-----------+-----------+-----------+----------+

現在我正在努力編寫一個查詢，該查詢返回所有未鎖定的錢包（即locked=false）以及所有相關金額，但金額是根據狀態過濾的（例如，僅包括具有status='new'或的金額status='accepted'）。

我試過類似的東西：

SELECT *

FROM "wallet"

LEFT JOIN "amount"

ON "wallet".id="amount"."walletId"

WHERE "amount"."orderId" IN (
   SELECT id FROM "order"
   WHERE "order".status='new' OR "order".status='accepted'
)

AND "wallet".locked=false;

但這並不完全符合我的要求，因為所有錢包結果也會根據訂單狀態條件進行過濾。我只希望根據訂單狀態條件過濾金額結果。這樣的事情甚至可能嗎？或者我需要兩個單獨的查詢嗎？

更新

我想我已經設法通過將查詢更改為：

SELECT *

FROM "wallet"

LEFT JOIN "amount"

ON "wallet".id="amount"."walletId"

AND "amount"."orderId" IN (
   SELECT id
   FROM "order"
   WHERE status='new'
   OR status='accepted'
)

WHERE "wallet".locked=false;

現在我唯一不確定的是這IN (SELECT ...)部分是否足夠高效。我已經讀過，當表（即“訂單”）相對較大時，IN基於方法會變得很慢。

您可以使用EXISTS而不是IN. 這可能會表現得更好。

SELECT *
      FROM "wallet"
           LEFT JOIN "amount"
                     ON "wallet"."id" = "amount"."walletId"
                        AND EXISTS (SELECT *
                                           FROM "order"
                                           WHERE "order"."id" = "amount"."orderId"
                                                 AND "order"."status" IN ('new',
                                                                          'accepted'))
      WHERE "wallet"."locked" = false;

為了進一步提高上述性能，可以嘗試使用"wallet" ("locked", "id"),"amount" ("walletid", "orderid")和的索引order ("id", "status")。當然，除非你已經擁有它們。

如果您不確定哪個版本性能更好以及哪些索引有幫助，請檢查計劃。

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