Postgresql
選擇具有嵌套關係的查詢
我有以下 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")
。當然,除非你已經擁有它們。如果您不確定哪個版本性能更好以及哪些索引有幫助,請檢查計劃。