MySQL统计了两个不同的列，它们都与一个id有关

Question

我有一些用户，比如用户10、20和30；并且可以在a栏或b栏上登记；我需要计算每个用户在a列上的行数(simple count(((，但我也想计算同一用户在b列上有多少行；我的挑战是在同一行的a列和b列中有不同的用户。

+----+----+------+------+
| id | a  | b    | c    |
+----+----+------+------+
|  1 | 10 | NULL | NULL |
|  2 | 10 |   20 | NULL |
|  3 | 20 | NULL | NULL |
|  4 | 10 |   30 | NULL |
|  5 | 20 | NULL | NULL |
|  6 | 30 |   20 | NULL |
|  7 | 10 |   20 | NULL |
+----+----+------+------+

我试过：

select a, count(if(a is not null,1,null)) aC, count(if(b is not null, 1, null)) bC 
from test 
group by a;

但如果不为空，则返回列b：

+----+----+----+
| a  | aC | bC |
+----+----+----+
| 10 |  4 |  3 |
| 20 |  2 |  0 |
| 30 |  1 |  1 |
+----+----+----+

我需要

+----+----+----+
| a  | aC | bC |
+----+----+----+
| 10 |  4 |  0 |
| 20 |  2 |  3 |
| 30 |  1 |  1 |
+----+----+----+

我已经设法通过带有where的嵌套选择获得了结果，但只有当我查询特定用户时才有效(结果只有一行(。

Answer 1

这里有一种方法：

select a 
, count(case when col ='A' then 1 end) Acount
, count(case when col ='B' then 1 end) BCount
from (
select a , 'A' col
from tablename
union all 
select b , 'B'
from tablename 
) t 
group by a 

Answer 2

嗯，可能表示设计不好。。。

您可以尝试在这里使用派生表。首先，您需要一个所有用户都使用UNION而不是a和b的表(如果您有一个存储所有用户的表，则可以使用它(。然后分别通过a或b将两个派生表进行左联接进行聚合组。

SELECT u.user,
coalesce(a.count, 0) AS ac,
coalesce(b.count, 0) AS bc
FROM (SELECT a AS user
FROM test
UNION
SELECT b AS user
FROM test) u
LEFT JOIN (SELECT a AS user,
count(*) AS count
FROM test
GROUP BY a) a
ON a.user = u.user
LEFT JOIN (SELECT b AS user,
count(*) AS count
FROM test
GROUP BY b) b
ON b.user = u.user;