我试图创建一个查询,按表中所有实体的关系类型返回计数。如:
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个类型id做同样的事情,再给两个SUM()s来显示针对一个实体的不同关系类型总数。如果我添加额外的LEFT join,就会得到重复的行,并且计数会被夸大。
希望这能说得通。有人能提出解决办法吗?
对于子查询,您可以这样做:
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