作者:Li_pengwei | 来源:互联网 | 2022-12-09 21:00
在我的mysql数据库中,我有instagram_actions_histories
带有两个重要列的表as action_type
和action_name
,现在我想使用count和case语句获取id
s列的计数,例如:
等于和等于“喜欢” id
时所有s的计数action_type
1
action_name
select `account_id`,
count(case when action_type = 1 and action_name='like' then id else 0 END) as `like`,
count(case when action_type = 1 and action_name='superLike' then id else 0 END) as `superLike`,
count(case when action_type = 2 then id else 0 END) as `follow`,
from `instagram_actions_histories`
where `instagram_actions_histories`.`account_id` in (1)
group by `account_id`
不幸的是我得到错误的结果,因为所有输出都是相同的,例如:
account_id like superLike follow
1 1282 1282 1282
正确的结果应该是:
account_id like superLike follow
1 1282 20 10
Tim Biegelei..
5
您应该为一场比赛数1,NULL
在没有一场比赛时数:
SELECT
account_id,
COUNT(CASE WHEN action_type = 1 AND action_name = 'like' THEN 1 END) AS `like`,
COUNT(CASE WHEN action_type = 1 AND action_name = 'superLike' THEN 1 END) AS superLike,
COUNT(CASE WHEN action_type = 2 THEN 1 END) AS follow
FROM instagram_actions_histories
WHERE account_id IN (1)
GROUP BY account_id;
当前CASE
表达式的逻辑存在的问题是COUNT
将任何非null值都计为一个计数。因此,零也将被计算在内。
注意,您当前的逻辑本来可以SUM
用来进行条件聚合,例如
SUM(CASE WHEN action_type = 1 AND action_name = 'like'
THEN 1 ELSE 0 END) AS `like`
在这种情况下,要关闭不匹配记录的汇总,实际上我们可以使用零,因为将零相加不会影响总和。
1> Tim Biegelei..:
您应该为一场比赛数1,NULL
在没有一场比赛时数:
SELECT
account_id,
COUNT(CASE WHEN action_type = 1 AND action_name = 'like' THEN 1 END) AS `like`,
COUNT(CASE WHEN action_type = 1 AND action_name = 'superLike' THEN 1 END) AS superLike,
COUNT(CASE WHEN action_type = 2 THEN 1 END) AS follow
FROM instagram_actions_histories
WHERE account_id IN (1)
GROUP BY account_id;
当前CASE
表达式的逻辑存在的问题是COUNT
将任何非null值都计为一个计数。因此,零也将被计算在内。
注意,您当前的逻辑本来可以SUM
用来进行条件聚合,例如
SUM(CASE WHEN action_type = 1 AND action_name = 'like'
THEN 1 ELSE 0 END) AS `like`
在这种情况下,要关闭不匹配记录的汇总,实际上我们可以使用零,因为将零相加不会影响总和。