Postgresql

選擇具有嵌套關係的查詢

  • April 1, 2019

我有以下 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