在我的應用程式projects中，與widgets. 每個專案都包含許多小部件。
在我的應用程式中，我使用復合鍵（代碼、slug）來鍵入一個小部件，即(p1, w2)并且(p2, w2)是不同的。專案p1有自己的小部件w2，專案p2有不同的小部件w2，代碼相同。這為專案所有者提供了單獨的范圍。
我想要一個查詢，它將通過復合鍵獲取一個小部件，同時區分projects.slug不匹配與widgets.code不匹配。
這是我到目前為止所嘗試的。WHERE 子句過濾特定專案 slug 和小部件代碼，但無法確定slug是缺少專案還是缺少小部件code。
w2下面的第一個查詢回傳一個空結果，因為project沒有小部件p3。但是，下面的第二個查詢也回傳一個空結果集，但原因不同；專案表中不存在專案 slug。
test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id
WHERE p.slug = 'p3' AND code = 'w2';
id | slug | id | code
---- ------ ---- ------
(0 rows)
test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id
WHERE p.slug = 'missing-project' AND code = 'w2';
id | slug | id | code
---- ------ ---- ------
(0 rows)
什么查詢將產生可以區分不存在的專案 slug、具有不存在的小部件代碼的現有專案以及專案 slug 和小部件代碼的完全匹配的結果？
這個問題的背景：
我已經學會了如何使用查詢來獲取專案的所有小部件。（如何區分無行和不存在外鍵參考？）
如果我使用僅帶有 slug 的查詢，則我檢測到專案何時丟失（空結果集 - 下面的第二條陳述句）與專案在那里但沒有小部件（1 行，id 列為空 - 下面的第三條陳述句）。
test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id
WHERE p.slug = 'p1';
id | slug | id | code
---- ------ ---------- ----------
1 | p1 | 51 | w1
1 | p1 | 52 | w2
(2 rows)
test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id
WHERE p.slug = 'p3';
id | slug | id | code
---- ------ ---- ------
3 | p3 | 0 |
(1 row)
test=# SELECT p.id, p.slug, COALESCE(w.id, 0) id, COALESCE(w.code, '') code
FROM projects p LEFT OUTER JOIN widgets w ON p.id = w.project_id
WHERE p.slug = 'missing-project';
id | slug | id | code
---- ------ ---- ------
(0 rows)
SELECT
p.id, p.slug, COALESCE(w.id, 0), COALESCE(w.code, '')
FROM projects p
LEFT OUTER JOIN widgets w
ON p.id = w.project_id
WHERE p.slug = 'p4';
CREATE TABLE projects (
id INTEGER,
slug VARCHAR(32),
PRIMARY KEY (id)
);
CREATE TABLE widgets (
id INTEGER NOT NULL,
project_id INTEGER NOT NULL,
code VARCHAR(32) NOT NULL,
PRIMARY KEY (id, project_id),
CONSTRAINT widgets_project_id_fkey FOREIGN KEY (project_id)
REFERENCES projects (id)
);
INSERT INTO projects (id, slug) VALUES (1, 'p1');
INSERT INTO projects (id, slug) VALUES (2, 'p2');
INSERT INTO projects (id, slug) values (3, 'p3');
INSERT INTO widgets (id, project_id, code) VALUES (51, 1, 'w1');
INSERT INTO widgets (id, project_id, code) VALUES (52, 1, 'w2');
INSERT INTO widgets (id, project_id, code) VALUES (53, 2, 'w2');
uj5u.com熱心網友回復：
如果我理解得很好，您想區分三種情況：
- 專案和小部件存在。
- 該專案存在，但小部件不存在。
- 該專案不存在。
如果這是對案例的正確描述，您可以將小部件搜索放在ON聯接子句中，并將專案搜索放在WHERE查詢子句中。
例如：
SELECT
p.id,
p.slug,
COALESCE(w.id, 0) as id,
COALESCE(w.code, '') as code
FROM projects p
LEFT OUTER JOIN widgets w ON p.id = w.project_id
AND w.code = 'w1' -- widget code
where p.slug = 'p3' -- project code
案例#1 結果：
id slug id code
-- ---- -- ----
1 p3 10 w1
案例 #2 結果：
id slug id code
-- ---- -- ----
1 p3 10 <null>
案例#3 結果：
id slug id code
-- ---- -- ----
-- 0 rows returned
盡管它仍然回傳一行，但案例#2 可以通過code顯示為空的列與案例#1 區分開來。案例#3 根本不回傳任何行。
請參閱db<>fiddle的運行示例。
您最初方法的問題在于，該WHERE條件w.code = 'w2'有效地將您的左外連接變成了內連接：任何widget側面有 NULL 的結果都將被該條件消除。這就是為什么你從來沒有得到這些結果。如果您將該條件放在連接條件中，則不會消除此類結果。原因是左外連接的定義方式：它們是內連接的結果，加上結果中缺少的左側的行，并在右側補充了 NULL。您的條件會從內部聯接中消除該行，因此它出現在結果中，widget旁邊有 NULL。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/510788.html
上一篇：修復傾斜/傾斜的文本Opencv