如何为SQL结果添加membership标识并统计userid出现次数？

现有app_user_project表结构及测试数据如下：

CREATE TABLE IF NOT EXISTS `app_user_project` (
`userid` bigint(20) NOT NULL,
`projectid` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `app_user_project` (`userid`, `projectid`) VALUES
(1, 1),(2, 1),(3, 1),(4, 1),(5, 1),(6, 1),(1, 2),(2, 2),(3, 2),(4, 2);

需求：生成包含以下字段的结果集：

• userid：用户ID
• projectid：项目ID
• membership：当projectid为1时标记为isMember，否则为false
• numberOfMembership：对应用户在表中的总出现次数

现有两个查询：

1. 统计用户出现次数（按userid分组，共6行）：

SELECT userid, COUNT(userid) AS numberOfMembership 
FROM app_user_project 
GROUP BY userid

2. 生成带membership字段的结果（共10行）：

SELECT userid, projectid, 
       IF(projectid = '1', 'isMember', 'false') AS membership
FROM app_user_project 
GROUP BY userid, projectid 
ORDER BY membership

由于两个查询行数不同，需要合并获取包含所有字段的最终结果。

解决方案 #

方法1：通过JOIN关联统计子查询 #

将带membership的基础查询与统计用户次数的子查询通过userid关联，确保每行数据都能匹配到对应用户的总次数：

SELECT 
    a.userid,
    a.projectid,
    IF(a.projectid = '1', 'isMember', 'false') AS membership,
    b.numberOfMembership
FROM 
    app_user_project a
JOIN 
    (SELECT userid, COUNT(userid) AS numberOfMembership 
     FROM app_user_project 
     GROUP BY userid) b ON a.userid = b.userid
GROUP BY 
    a.userid, a.projectid
ORDER BY 
    membership;

方法2：使用窗口函数（MySQL 8.0+ 支持） #

利用窗口函数COUNT() OVER()直接在原查询中计算用户的总出现次数，无需额外子查询，更简洁高效：

SELECT 
    userid,
    projectid,
    IF(projectid = '1', 'isMember', 'false') AS membership,
    COUNT(userid) OVER(PARTITION BY userid) AS numberOfMembership
FROM 
    app_user_project
GROUP BY 
    userid, projectid
ORDER BY 
    membership;

预期结果示例 #

内容的提问来源于stack exchange，提问作者jrg