mysql - 计算包含特定字符串和聚合的行数

Question

我的数据库中有以下一组行:

 USER   |  ACTIVITY
'User1' | 'User1 liked FeatureX'
'User1' | 'User1 liked FeatureY'
'User2' | 'User2 followed FeatureX'

我想运行执行以下操作的查询:

select User, 
       count(Activity like '%liked%') as LikedActivity, 
       count(Activity like '%followed%') as FollowedActivity
from db.Table 
group by USER

换句话说:我想检索每个用户的“喜欢的功能”和“关注的功能”的数量。

Answer 1

在这里使用 SUM 和 CASE 表达式:

SELECT
    User, 
    SUM(CASE WHEN Activity LIKE '%liked%'    THEN 1 ELSE 0 END) AS LikedActivity, 
    SUM(CASE WHEN Activity LIKE '%followed%' THEN 1 ELSE 0 END) AS FollowedActivity
FROM db.Table 
GROUP BY User;

我们也可以在这里使用 COUNT，这将导致一个看起来更整洁的查询:

SELECT
    User, 
    COUNT(CASE WHEN Activity LIKE '%liked%'    THEN 1 END) AS LikedActivity, 
    COUNT(CASE WHEN Activity LIKE '%followed%' THEN 1 END) AS FollowedActivity
FROM db.Table 
GROUP BY User;