从两个表中选择Count不同的值。

Users Table:

用户表:

+-----------+----------+------------+-------------+
| name      | fb_id    | date       | flipbook    |
+-----------+----------+------------+-------------+
| Tia       | 66783722 | 1975-09-18 |  june 2014  |
| Nikki     | 10438259 | 1972-03-04 |  july 2014  |
| Yamila    | 73370629 | 1972-03-04 | august 2014 | 
+-----------+----------+------------+-------------+

Visits Table:

访问表:

+-----------+----------+------------+-------------+
| Name      | fb_id    | Date       |  Flipbook   |
+-----------+----------+------------+-------------+
| Tia       | 66783722 | 1975-09-18 | june 2014   |
| Nikki     | 10438259 | 1972-03-04 | august 2014 |
| Nikki     | 10438259 | 1972-04-04 | october 2014|
+-----------+----------+------------+-------------+

I want the query to return all users from from user Table and count the number of flipbooks, for example:

我希望查询从用户表返回所有用户，并计算flipbooks的数量，例如:

[1]
name => Tia, 
fb_id = 66783722,
date => 1975-09-18,
count_flip => 1 (june 2014 is in both tables so we count it as 1)

[2]
name => Nikki, 
fb_id = 10438259,
date => 1972-03-04,
count_flip => 3 (because in the first table we have june 2014 and in the second table we have august 2014 and october 2014, so no duplicates)

[3]
name => Yamila, 
fb_id = 73370629,
date => 1972-03-04,
count_flip => 1 (because we have august 2014 in the first table and she is not mentioned in the second one)

I tryed to do this query:

我试图做这个查询:

SELECT u.*, (SELECT COUNT(Distinct flipbook) FROM visits v WHERE v.fb_id = u.fb_id) as count_flip FROM users u

But the problem is I am missing the users that are in the users table but not in the visits table. So for the example above I wouldn't see "Yamila" in my query.

但问题是我缺少的是用户表中的用户，而不是访问表中的用户。因此，对于上面的示例，我在查询中看不到“Yamila”。

Any thoughts how to fix this?

有什么想法吗?

2 个解决方案

#1

Another way of thinking about the logic is that you want to count 1 for the users table and then add up all non-matching flipbook values for the name in the visits table. This suggests a correlated subquery:

考虑逻辑的另一种方式是，您希望为users表计数1，然后为visit表中的名称添加所有不匹配的flipbook值。这就提出了相关子查询:

select u.*,
       (select 1 + count(*)
        from visits v
        where v.name = u.name and v.flipbook <> u.flipbook
       ) as nr
from users u;

EDIT:

编辑:

Another way to do what you want is less efficient (assuming you have the right indexes for the above query):

另一种做你想做的事情的方法是效率较低(假设你对上述查询有正确的索引):

select u.*, uv.nr
from users u join
     (select u.name, count(distinct flipbook) as nr
      from ((select name, flipbook from users) union all
            (select name, flipbook from visits)
           ) uv
      group by u.name
     ) uv
     on u.name = uv.name;

#2

I think you can just do this:-

我想你可以这么做:-

SELECT users.name, users.fb_id, users.date, COUNT(DISTINCT visits.Flipbook) + 1 AS count_flip
FROM users
LEFT OUTER JOIN visits
ON users.fb_id = visits.fb_id
AND users.flipbook != visits.flipbook
GROUP BY users.name, users.fb_id, users.date

Does a LEFT OUTER JOIN to get a row for each matching user with a different date. Then just ad one to the resulting count (to count the original field on the users table).

为每个匹配的用户提供一个不同的日期的左外连接。然后，仅将一个ad映射到结果计数(以计算users表上的原始字段)。

This avoids any sub queries.

这避免了任何子查询。

#1