mysql - 分组、计数和位置

Question

我被难住了，挠着头。它一定非常简单，但我没有看到。

假设我有四个表:

video = id
hastag = id, tag_id, video_id
hasteam = id, team_id, video_id
hasidol = id, idol_id, video_id

此数据集(仅作为示例):

video = (1), (2), (3)
hastag = (1, 1, 1), (2, 1, 2), (3, 2, 3)
hasteam = (1, 1, 1), (2, 1, 3), (3, 2, 2)
hasidol = (1, 1, 3)

这个查询:

SELECT v.id ,
COUNT(vhtag.id),
COUNT(vhteam.id),
COUNT(vhidol.id)
FROM video v
LEFT JOIN hastag vhtag ON vhtag.video_id = v.id
LEFT JOIN hasteam vhteam ON vhteam.video_id = v.id
LEFT JOIN hasidol vhidol ON vhidol.video_id = v.id

WHERE
v.id <> 1
AND
(
  vhtag.tag_id IN (SELECT htt2.tag_id FROM hastag htt2 WHERE video_id = 1)
  OR
  vhteam.team_id IN (SELECT htt3.team_id FROM hasteam htt3 WHERE video_id = 1)
  OR
  vhidol.idol_id IN (SELECT htt4.idol_id FROM hasidol htt4 WHERE video_id = 1)
)
GROUP BY v.id

它为我提供了与 WHERE 子句不对应的“有”行的计数。例如，如果视频行只有一个视频 ID 为 1 的团队和一个完全不相关的标签，它会给我“common tag count: 1, common team count: 1”，而它应该说“common tag”计数:0(因为它是一个不相关的标签)，普通团队计数:1”。

现在，只要我将查询限制为只有一个“有”表，就像这样:

SELECT v.id ,
COUNT(vhtag.id)
FROM video v
LEFT JOIN hastag vhtag ON vhtag.video_id = v.id

WHERE
v.id <> 1
AND
(
  vhtag.tag_id IN (SELECT htt2.tag_id FROM hastag htt2 WHERE video_id = 1)
)
GROUP BY v.id

然后它确实起作用了，但问题是当我尝试将多个“有”表放入查询中时。我试过使用 HAVING，但它无法识别“vhtag.tag_id”列。我显然在这里做错了什么，任何人都可以帮助我吗？

编辑:

此类作品:

LEFT JOIN hastag vhtag ON vhtag.video_id = v.id AND vhtag.tag_id IN (SELECT htt2.tag_id FROM hastag htt2 WHERE video_id = 1)
LEFT JOIN hasteam vhteam ON vhteam.video_id = v.id AND vhteam.team_id IN (SELECT htt3.team_id FROM hasteam htt3 WHERE video_id = 1)
LEFT JOIN hasidol vhidol ON vhidol.video_id = v.id AND vhidol.idol_id IN (SELECT htt4.idol_id FROM hasidol htt4 WHERE video_id = 1)

而且我也可以在 Doctrine 中使用它(我很笨，忘记了 WITH)。这是最优方法吗？

Answer 1

您遇到了一个问题，即您的联接导致每个团队内出现交叉联接。

最简单的方法是不重复计数:

SELECT v.id , COUNT(distinct vhtag.id), COUNT(distinct vhteam.id),
       COUNT(distinct vhidol.id)
FROM video v LEFT JOIN
     hastag vhtag
     ON vhtag.video_id = v.id
     LEFT JOIN hasteam vhteam
     ON vhteam.video_id = v.id
     LEFT JOIN hasidol vhidol
     ON vhidol.video_id = v.id 

真正的解决方案是分别聚合每个值，然后将结果连接在一起。