具有同一列SQL Server的多个JOIN [英] Multiple JOINs with same column SQL Server

问题描述

我必须从GraphNodes中选择id，而在GraphEdges中的Source_Node和Target_Node两列中存在相同的id.表的结构如下:

I have to select id from GraphNodes whereas same id exists in GraphEdges in two columns i.e. Source_Node and Target_Node. The structure of tables are as follows:

GraphNodes

+---+---------+-------------------+------------+-----------+
| id | Node_ID | Node             | Node_Label | Node_Type |
+---+---------+-------------------+------------+-----------+
| 1  |  677    | Nuno Vasconcelos | Author     |    1      |
| 2  |  1359   | Peng Shi         | Author     |    1      |
| 3  |  6242   | Z. Q. Shi        | Author     |    1      |
+----+---------+------------------+------------+-----------+    

GraphEdges

+------------+------------------+-------------+------------------+------+-----------+
|Source_Node | Source_Node_Type | Target_Node | Target_Node_Type | Year | Edge_Type |
+------------+------------------+-------------+------------------+------+-----------+
| 1          |  1               |  10965      |     2            | 2005 |   1       |
| 1          |  1               |  10179      |     2            | 2007 |   1       |
| 1          |  1               |  10965      |     2            | 2007 |   1       |
+------------+------------------+-------------+------------------+------+-----------+  

我只为两个表显示了3行，只是为了了解表的结构.我已将查询用作:

I have shown only 3 rows for both tables just to get an idea of table's structures. I have used query as:

SELECT GN.id as Node_ID, 
       COUNT(DISTINCT(CONCAT(GE.Source_Node, '-', GE.Target_Node))) 
       AS Mutual_Links -- OR Node_Degree
FROM GraphEdges GE
JOIN GraphNodes GN ON GN.id = GE.Source_Node --How to JOIN this
                  AND GN.id = GE.Target_Node --How to JOIN this
WHERE (Source_Node IN (SELECT id FROM GraphNodes 
                       WHERE id BETWEEN 1 AND 510) 
                       AND Edge_Type IN (1, 2, 3))
OR (Target_Node IN (SELECT id FROM GraphNodes 
                    WHERE id BETWEEN 1 AND 510) 
                    AND Edge_Type IN (1, 2, 3))
GROUP BY GN.id

我想要以以下形式输出:

I want output in the form:

+--------+-------------+
|Node_ID | Mutual_Links|
+--------+-------------+
|  1     | 31          |
|  2     | 23          |
|  3     | 12          |
| ...    | ...         |
+--------+-------------+  

问题在于，如何将GraphEdges与GraphNodes结合在一起，我可以从GraphNodes获得id或Node_ID并从GraphEdges获得COUNT DISTINCT Mutual_Links.

The issue is that how to join GraphEdges with GraphNodes that I can get id OR Node_ID form GraphNodes and COUNT DISTINCT Mutual_Links from GraphEdges.

推荐答案

这样的事情

;with thedata ( id, MutualLinks ) 
as (Select id, MutualLinks = count(*) from [dbo].[GraphNodes]
inner join [dbo].[GraphEdges] on Source_node = node_id
group by id 
union all
Select id, MutualLinks = count(*)   from [dbo].[GraphNodes]
inner join [dbo].[GraphEdges] on target_node = node_id
group by id )
Select id, total = sum(MutualLinks)
from thedata
group by id

这篇关于具有同一列SQL Server的多个JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！