mysql - 使用子查询中的 GROUP BY 查询获取行数

Question

我正在使用子查询构建 MySQL 查询。查询需要，如Getting the number of rows with a GROUP BY query中所述，分组查询返回的记录数，因为我想要数据库中记录的天数。所以我使用以下内容:

SELECT
  COUNT(*)
FROM
(
  SELECT
    cvdbs2.dateDone
  FROM
    cvdbStatistics cvdbs2
  WHERE
    cvdbs2.mediatorId = 123
  GROUP BY
    DATE_FORMAT( cvdbs2.dateDone, "%Y-%d-%m" )
) AS activityTempTable

现在，我希望将其作为子查询，因为我需要具有不同 WHERE 语句的更多数据。所以我的查询变成:

SELECT
  x,
  y, 
  z,
  (
    SELECT
      COUNT(*)
    FROM
    (
      SELECT
        cvdbs2.dateDone
      FROM
        cvdbStatistics cvdbs2
      WHERE
        cvdbs2.mediatorId = mediators.id
      GROUP BY
        DATE_FORMAT( cvdbs2.dateDone, "%Y-%d-%m" )
    ) AS activityTempTable
  ) AS activeDays
FROM
  mediators
LEFT JOIN
  cvdbStatistics
ON
  mediators.id = cvdbStatistics.mediatorId
WHERE
  mediators.recruiterId = 409
GROUP BY
  mediators.email

(我在查询中遗漏了一些不相关的 WHERE 语句。409 只是一个示例 ID，这是由 PHP 插入的)。

现在，我收到以下错误:

#1054 - Unknown column 'mediators.id' in 'where clause' 

MySQL 忘记了最深子查询中的 mediators.id。如何构建一个查询，其中包含 GROUP-BY 查询的结果数(该查询需要主查询中的值作为结果之一)？为什么最深的查询不知道“mediators.id”？

Answer 1

尝试以下操作:

SELECT
  x,
  y, 
  z,
  (
    SELECT
      COUNT(distinct DATE_FORMAT( cvdbs2.dateDone, "%Y-%d-%m" ))
    FROM
      cvdbStatistics cvdbs2
    WHERE
      cvdbs2.mediatorId = mediators.id
  ) AS activeDays
FROM
  mediators
LEFT JOIN
  cvdbStatistics
ON
  mediators.id = cvdbStatistics.mediatorId
WHERE
  mediators.recruiterId = 409
GROUP BY
  mediators.email