SQL 多列联接和重命名或映射联接列 - 更快的单查询

Question

我有两个表：

[节点]

row_index | obj_index    | obj_name    | obj_age | other | type
0         | 1450         | dog         | 2.7     | 943   | animal
1         | 2954         | cat         | 3.5     | 20    | animal
2         | 5613         | train       | 1.8     | 540   | vehicle
3         | 7852         | mouse       | 4.3     | 2     | animal
4         | 2380         | money       | 3.2     | 12    | currency
5         | 6341         | jetta       | 1.8     | 40    | vehicle

[边缘]

row_index | obj1_index   | obj2_index  | edge_prop 
0         | 1450         | 2954        | 18
1         | 2954         | 5613        | 1
2         | 2380         | 6341        | 3
3         | 5613         | 6341        | 56
4         | 1450         | 7852        | 26
5         | 2380         | 7852        | 6
6         | 7852         | 2954        | 7
7         | 6341         | 2954        | 9

我试图得到的输出是这样的：

obj1_index | obj2_index | edge_prop | obj_name_x | obj_age_x | obj_name_y | obj_age_y
1450       | 2954       | 18        | dog        | 2.7       | cat        | 3.5
7852       | 2954       | 7         | mouse      | 4.3       | cat        | 3.5
6341       | 2954       | 9         | jetta      | 1.8       | cat        | 3.5
1450       | 7852       | 26        | dog        | 2.7       | mouse      | 4.3
2954       | 5613       | 1         | cat        | 3.5       | train      | 1.8
5613       | 6341       | 56        | train      | 1.8       | jetta      | 1.8

本质上，我想获取具有给定属性的某些节点之间的边缘，因此内部查询可能是：

SELECT * FROM Nodes WHERE type IS IN 'animal' OR 'vehicle'

然后一个主要问题是沿着obj1和obj2索引连接它，并命名列......

这是我尝试使用的 SQL Statment，但它不正确 - 并且由于必须运行两次内部查询，因此可能非常慢(sqlite 数据库只有几十亿行，但仍然会有一些性能(：

SELECT E.*, N1.object_name AS obj_name_2, N2.object_name AS obj_name_2 FROM Edges E 
INNER JOIN (
SELECT * FROM Nodes
WHERE type IN ('animal', 'vehicle')) AS N1
ON (E.obj1_index = N1.obj_index AND E.obj2_index = N1.obj_index)
INNER JOIN (
SELECT * FROM Nodes
WHERE type IN ('animal', 'vehicle')) AS N2
ON (E.obj2_index = N2.obj_index AND E.obj2_index = N2.obj_index)

但它不起作用。

对此进行更好的SQL查询的任何建议？

csv 中的表字符串如下，因为上面不是最适合在 SQL 中尝试操作的首选数据结构：

节点：

,row_index,obj_index,obj_name,obj_age,other,typen0,0,1450,dog,2.7,943,animaln1,1,2954,cat,3.5,20,animaln2,2,5613,train,1.8,540,vehiclen3,3,7852,mouse,4.3,2,animaln4,4,2380,money,3.2,12,currencyn5,5,6341,jetta,1.8,40,vehiclen

边缘：

,row_index,obj1_index,obj2_index,edge_propn0,0,1450,2954,18n1,1,2954,5613,1n2,2,2380,6341,3n3,3,5613,6341,56n4,4,1450,7852,26n5,5,2380,7852,6n6,6,7852,2954,7n7,7,6341,2954,9n

Answer 1

我认为你只需要两个连接：

select e.*, n1.*, n2.*
from edges e join
nodes n1
on e.obj1_index = n1.obj_index join
nodes n2
on e.obj2_index = n2.obj_index
where n1.type in ('animal', 'vehicle') and
n2.type in ('animal', 'vehicle') ;