使用 GROUP UNION 和 SUM 汇总单个 SQL 表上的多个向量

Question

我正在尝试创建一个查询，该查询按关系类型返回表中所有实体的计数。例如。

1|VenueName1|0|0|0|

其中 0,0,0 是找到的关系数的计数。

该表本身包含许多不同外部项目类型的外部关系。每个关联可以以“向前”或“向后”的方式出现在表中，因此，例如，每个关系可以以两种方式之一出现：

|objectTypeId|objectId|associatedId  |associatedTypeId|
|   13       |   1    |     8        |      2         |
|   2        |   8    |     1        |      13        |

有意义吗？它们通常只会出现一次，但可能出现在任一“方向”。因此，为了获得给定类型的总关系，我使用类似于以下的 UNION：

SELECT ip.id, ip.name, SUM(totalUnion.EventTotals) as Events
 FROM iplace ip

 LEFT JOIN

(

SELECT object_id, count(*) as EventTotals FROM `iassociation` ia
WHERE object_type_id=2 AND associated_object_type_id=14
GROUP BY object_id
UNION ALL

SELECT associated_object_id, count(*) as EventTotals FROM `iassociation` ia
WHERE associated_object_type_id=2 AND object_type_id=14
GROUP BY associated_object_id


) totalUnion ON ip.id = totalUnion.object_id


WHERE ip.type_id IN (4,7,11,15,16) 
GROUP BY ip.id

工作正常。我的问题是我想对另外 2 个 typeId 执行相同的操作，再给出两个 SUM() 来显示不同的针对一个实体的关系类型总计。如果我添加额外的 LEFT JOIN，则会出现行重复并且计数被夸大。

希望这是有道理的。有人能提出解决方案吗？

Answer 1

对于您的子查询，您可以执行以下操作：

SELECT
    object_id,
    SUM(CASE WHEN object_type_id = 2 AND associated_object_type = 14 THEN 1 ELSE 0 END) AS event_1,
    SUM(CASE WHEN object_type_id = 5 AND associated_object_type = 8 THEN 1 ELSE 0 END) AS event_2
FROM
    iassociation
UNION ALL
GROUP BY
    object_id
SELECT
    associated_object_id,
    SUM(CASE WHEN associated_object_type = 2 AND object_type_id  = 14 THEN 1 ELSE 0 END) AS event_1,
    SUM(CASE WHEN associated_object_type = 5 AND object_type_id = 8 THEN 1 ELSE 0 END) AS event_2
FROM
    iassociation
GROUP BY
    associated_object_id