mysql - 使用内连接获取同一列的计数

Question

我有一个像这样的表，我需要在其中设置主键 id 的男性和女性计数:摘要表:

    id   femaleCount   maleCount
    -----------------------------
    s1      ?            ?
    s2      ?            ?

... and so on

有一个如下detail表，其中包含与summaryTable的每个id对应的用户:

    id    parentId   userId
    --------------------------
    1     s1         u1
    2     s1         u2
    3     s2         u2
    4     s2         u2

...and so on

第三个是用户表，如下所示:用户表:

userId   gender
-------------
u1       M
u2       F
u3       F
..and so on

我必须用男性和女性的计数更新汇总表。因此，根据上面的内容，对于 id=s1，femaleCount 应设置为 1 ，maleCOunt=1对于 id=s2，femaleCOunt 应设置为 2，maleCount=0

这可以在 MySQL 中使用 UPDATE 查询来完成吗？

我尝试了以下操作，但这会返回用户出现次数的总和，即如果 u1 对于 p1(比如说)出现 2 次，那么它将返回计数为 2 而不是 1:

SELECT 
  d.parentId,
  SUM(gender = 'F') AS 'F#', 
  sum(gender = 'M') as 'M#'
FROM detailsTable as d 
JOIN userTable as c on c.userId = d.userId
GROUP BY d.parentId;

也尝试了如下，但出现错误:

select d.parentId, 
    count(case when c.gender='M' then 1 end) as male_cnt,
    count(case when c.gender='F' then 1 end) as female_cnt,
 from detailsTable d, userTable c where d.userId=c.userId group by d.parentId, d.userId ;

此外，我的问题不仅仅限于选择，我还需要获取值，然后在汇总表中更新这些值。

Answer 1

我可能对 MySql 的语法很生疏，但我相信这可以满足您的需要。 CASE/SUM 实际上是获取计数的枢纽，然后您可以像平常一样更新表。

UPDATE  summaryTable AS st
        INNER JOIN ( SELECT parentId
                           ,SUM(CASE WHEN gender = 'f' THEN 1
                                     ELSE 0
                                END) femaleCount
                           ,SUM(CASE WHEN gender = 'm' THEN 1
                                     ELSE 0
                                END) maleCount
                     FROM   userTable d
                            INNER JOIN (SELECT DISTINCT parentId, userId FROM detail) ut ON d.userId = ut.userId
                     GROUP BY parentId
                   ) AS c ON c.parentId = st.parentId
SET     femaleCount = c.femaleCount
       ,maleCount = c.maleCount