我在上一個問題中得到了一些很大的幫助。這個建立在此之上。我想檢索連接查詢的資訊，但我不知道這在 SQL 中是否可行。
這是一個帶有平民的虛擬資料庫。每行都包含他們的姓名、城市、街道、門牌號和作業。在 SQLite 中生成資料庫的最小示例：
import sqlite3
from pathlib import Path
def main():
build_cmd = """CREATE TABLE civs (
name TEXT,
city TEXT,
street TEXT,
snumber INTEGER,
job TEXT) """
pdb = Path("dummy.db")
if pdb.exists():
pdb.unlink()
conn = sqlite3.connect(str("dummy.db"))
cur = conn.cursor()
cur.execute(build_cmd)
insert_cmds = """
BEGIN TRANSACTION;
INSERT INTO civs VALUES ('john', 'dublin', 'butchers_street', '42', 'butcher');
INSERT INTO civs VALUES ('marie', 'london', 'butchers_street', '3', 'baker');
INSERT INTO civs VALUES ('keith', 'london', 'camel_road', '7', 'carpenter');
INSERT INTO civs VALUES ('anna', 'dublin', 'butchers_street', '9', 'butcher');
INSERT INTO civs VALUES ('steve', 'london', 'maceys_alley', '1', 'blacksmith');
INSERT INTO civs VALUES ('beth', 'dublin', 'butchers_street', '68', 'grocer');
INSERT INTO civs VALUES ('lisa', 'dublin', 'church_street', '32', 'nun');
COMMIT;
"""
cur.executescript(insert_cmds)
cur.close()
conn.commit()
conn.close()
if __name__ == '__main__':
main()
假設我想在一排中檢索（所以每個住在約翰街的人一排）。我使用“a”約翰，因為理論上你當然可以有多個約翰。
- 約翰居住的街道
- 約翰居住的城市
- 約翰的作業
- 與約翰住在同一街道和城市的人的名字
- 與約翰住在同一街道和城市的人的作業
我到此為止，它檢索主查詢的資訊。
SELECT
civs.street, civs.city, civs.name, civs.job
FROM civs
JOIN
(SELECT city, street
FROM civs
WHERE name='john') jcivs
ON civs.street = jcivs.street
AND civs.city = jcivs.city
這使
| 街道 | 城市 | 姓名 | 作業 |
|---|---|---|---|
| 屠夫街 | 都柏林 | 安娜 | 屠夫 |
| 屠夫街 | 都柏林 | 貝絲 | 雜貨商 |
| 屠夫街 | 都柏林 | 約翰 | 屠夫 |
但是“約翰的作業”嚴重缺失，我將約翰作為單獨的一排，我不希望這樣，因為我們對約翰的鄰居感興趣。所以我不確定如何jcivs從最外面的 SELECT 中參考。所以這不起作用jcivs，因為外部 SELECT 不存在：
SELECT
civs.street, civs.city, jcivs.job, civs.name, civs.job
預期結果：
| 街道 | 城市 | 約翰喬布 | 姓名 | 作業 |
|---|---|---|---|---|
| 屠夫街 | 都柏林 | 屠夫 | 安娜 | 屠夫 |
| 屠夫街 | 都柏林 | 屠夫 | 貝絲 | 雜貨商 |
注意：效率對我來說很重要。您可以假設它是一個冷表，并且所有列都已編入索引。
uj5u.com熱心網友回復：
不需要創建id列，因為 SQLite 為每個表提供了一個rowid列（這是表的實際主鍵）。
像這樣做一個簡單的自我加入：
SELECT c1.*
FROM civs c1 INNER JOIN civs c2
ON (c2.city, c2.street) = (c1.city, c1.street) AND c2.rowid <> c1.rowid
WHERE c2.name = 'john';
請參閱演示。
uj5u.com熱心網友回復：
這應該是一種方法：
WITH civs2 AS (
SELECT civs.*, row_number() OVER () as id FROM civs
)
SELECT
civs2.street, civs2.city, jcivs.job, civs2.name, civs2.job
FROM civs2
JOIN
(SELECT city, street, job, id
FROM civs2
WHERE name='john') jcivs
ON civs2.street = jcivs.street
AND civs2.city = jcivs.city
AND civs2.id <> jcivs.id
根據您的查詢，我們：
- 將作業添加到jcivs子查詢；和
- 通過創建具有基于行號的 ID 的 CTE 來演示 ID 的使用。然后，我們確保
civs.id <> jcivs.id
如果您的表中有一個 ID，那可能會更有效，因為我們可以將它添加到 joincivs.id <> jcivs.id中。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/480030.html
下一篇：處理空值時的SQL多連接