左连接乘法值 [英] left join multiplying values

问题描述

我有以下疑问 -

SELECT COUNT(capture_id) as count_captures 
  FROM captures 
 WHERE user_id = 9

...返回 5

SELECT COUNT(id) as count_items 
  FROM items 
 WHERE creator_user_id = 9

...返回 22

我尝试了以下查询 -

I tried the following query -

   SELECT COUNT(capture_id) as count_captures, 
          COUNT(items.id) as count_items 
     FROM captures 
LEFT JOIN items ON captures.user_id = items.creator_user_id 
    WHERE user_id = 9

...但它返回两列均以 110 作为值.我希望一栏中有 5 个，另一栏中有 22 个.我做错了什么?

...but it returns two columns both with 110 as the value. I would want 5 in one column and 22 in the other. What am I doing wrong?

推荐答案

我的下意识是子查询:

select count(capture_id) as count_captures, 
    (select count(id) as count_items
         from items i where i.creator_user_id = captures.user_id) as count_items 
from captures 
where user_id = 9

我不确定你能做些什么来避免这种情况.您看到了预期的(以及普遍期望的行为).

I'm not really sure what you can do to avoid this. You're seeing expected (and generally desired behavior).

当然，如果您知道两者中的 ID 不会重复，您可以使用 distinct:

Of course, if you know that the ID's in both won't repeat themselves, you can use distinct:

SELECT COUNT( DISTINCT capture_id) as count_captures, 
      COUNT( DISTINCT items.id) as count_items 
FROM captures 
LEFT JOIN items ON captures.user_id = items.creator_user_id 
    WHERE user_id = 9

这篇关于左连接乘法值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！