MySQL 查询 : Inner Join, 计数和分组依据

Question

我有以下查询来获取每个 formId 的条目数:

SELECT DISTINCT
       formId,
       COUNT(entryID)
FROM approvalDetails
GROUP BY formId;

我想扩展此查询以从另一个表中提取与 formId 关联的 userEmail(表 #2 称为首选项)

我已尝试以下操作，但计数值看起来不正确。

SELECT approvalDetails.formId,
       COUNT(approvalDetails.entryID),
       preferences.userEmail
FROM approvalDetails
     INNER JOIN preferences ON approvalDetails.formId = preferences.formId
GROUP BY approvalDetails.formId,
         preferences.userEmail;

样本数据

表 1 - 审批详细信息

formId    entryId
-         -
1         1
1         2
1         3
2         4
3         5

表 2 - 首选项(电子邮件地址对于 formId 来说并非唯一)

formId    userEmail
-         -
1         email1
2         email2
3         email3
3         email4
3         email4

需要输出

  formId    Count (entryID)  userEmail
    -         -              -
    1         3              email1
    2         1              email2
    3         1              email3
    3         1              email4

Answer 1

SELECT ap.formId,
       ap.entryID,
       preferences.userEmail
FROM
(
    SELECT formId,
           COUNT(entryID) entryID
    FROM approvalDetails
    GROUP BY formId
) ap
INNER JOIN preferences ON ap.formId = preferences.formId
GROUP BY ap.formId,
         preferences.userEmail;