mysql - SQL:从表中逐步计算唯一访问

Question

我有这张表(访问次数):

id  |       fb_id    |   flipbook    | 
----     ----------      ---------     
1         1123           november2014        
2         1123           november2014      
3         1127           november2014       
4         1124           november2014    
5         1126           november2014            
6         1123           december2014
7         1124           december2014
8         1125           december2014
9         1123           january2015  
10         1124           january2015
11        1125           january2015
12         1123           february2015
13         1125           february2015
14        1124           february2015
15        1127           february2015
16        1129           march2015
17        1123           march2015
18        1123           march2015
19        1124           march2015
20        1125           march2015
21        1126           march2015
22        1128           march2015

我们总共有5本动画书，查询后我想得到如下结果:

5: 10 (because we have 2 users that visited all the flipbooks = 10 visits)
4: 4 (only 1 user visited 4 flipbooks = 4 visits)
3: 0
2: 4 (2 users visited  2 flipbook = 4 visits )
1: 2 (2 users visited only 1 flipbook = 2 visits)

我没有任何要在此处发布以实现此结果的查询，有什么想法吗？

Answer 1

我会创建一个子查询，通过 fb_id 计算不同的活页簿。外部查询将计算内部计数并将它们相乘。如果您想在单个字段中获取结果，如 t.visited:no_of_visits 格式，而不需要任何额外的子查询，您可以使用 concat() 函数。

select t.visited, count(t.visited) * t.visited as no_of_visits
from
    (select fb_id, count(distinct flipbook) as visited
     from vistits group by fb_id) t
group by t.visited