SQL:查询有向图中的相邻节点

Question

我有一个带有节点{A, B, C, D, ...}的图和一个指定它们之间有向边的表。

| node_1 | node_2 |
|-----------------|
|      A | B      |
|      A | C      |
|      B | A      |
|      B | D      |
|      D | A      |

如果从A到B有一条边，我们就写A ~ B。因此，其中node_1 = A和node_2 = B的行意味着A ~ B。我区分以下类型的关系：

A = B if A ~ B and B ~ A
A > B if A ~ B and not B ~ A
A < B if B ~ A and not A ~ B

如何检索与给定节点相邻的所有节点及其关系类型？例如，上表中对A的查询应该返回

| node | type |
|------|------|
|    B | =    | (because A ~ B and B ~ A)
|    C | >    | (because A ~ C and not C ~ A)
|    D | <    | (because D ~ A and not A ~ D)

Answer 1

这里有一种方法：

select node, 
case when count(*) = 2       then '=' 
when max(ordertype) = 1 then '>'
when max(ordertype) = 2 then '<' end as type
from (select node2 node,
1 ordertype 
from nodes 
where node1 = 'A'
union all 
select node1, 
2 
from nodes 
where node2 = 'A') t 
group by node 
order by node 

Answer 2

嗯。您可以将条件逻辑与聚合一起使用：

select (case when node_1 = 'A' then node_2 else node_1 end) as other_node,
(case when count(*) = 2 then '='
when max(node_1) = 'A' then '>'
else '<'
end) as type
from nodes n
where 'A' in (node_1, node_2)
group by (case when node_1 = 'A' then node_2 else node_1 end);

这里有一个db<gt；不停摆弄

这似乎是最简单、可能也是性能最好的解决方案。