感谢您的帮助。 我有两个表,我想在内部将一个表连接到另一个表两次。
CREATE TABLE "indexlist" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"word" UNIQUE
)
INSERT INTO "indexlist" (words) VALUES
( 'run' ),
( 'hand' ),
( 'man' ),
( 'far' )
CREATE TABLE "nodes" (
"source" TEXT,
"Target" TEXT
)
INSERT INTO nodes ( source, target ) VALUES
( "run", "far" ),
( "far", "hand" );
我试过这个。 它不会引发错误消息,但不显示任何结果:
SELECT t.id, t.word,
t1.target, t2.source
FROM indexlist t
LEFT JOIN nodes t1 ON t1.target=t.word
LEFT JOIN nodes t2 ON t.word=t2.source
我想输出下表:
source_id | source | target | target_id
=======================================
1 | run | far | 4
4 | far | hand | 2
您必须使用nodes
到 2 个副本的indexlist
LEFT
连接:
SELECT i1.id source_id, n.source, n.target, i2.id target_id
FROM nodes n
LEFT JOIN indexlist i1 ON n.source = i1.word
LEFT JOIN indexlist i2 ON n.target = i2.word
结果:
source_id source Target target_id
1 run far 4
4 far hand 2