mysql - 如何使用嵌套查询从连接表中添加其他字段？

Question

我有以下代码，可以完美地计算在过去一年中完成多个志愿者轮类的志愿者(不是工作人员)的数量:

SELECT count(*) AS Volunteers
    FROM (
          SELECT COUNT(CASE WHEN (STR_TO_DATE(t2.date,'%m/%d/%Y') > SUBDATE(now(), INTERVAL 1 year)) AND t2.status="Completed" AND t1.sector<>"Staff" THEN t2.id END) AS count
          FROM t2
          LEFT JOIN t1 ON t1.id = t2.id
          GROUP BY t2.id
          HAVING (count > 1)
         ) AS Volunteers;

但是除了给我汇总计数之外，我不知道如何分解它。例如，我想看看是谁招募了这些人，但我不知道如何将其添加到这段代码中。这显然行不通:

SELECT t2.recruitedby, count(*) AS Volunteers
    FROM (
          SELECT COUNT(CASE WHEN (STR_TO_DATE(t2.date,'%m/%d/%Y') > SUBDATE(now(), INTERVAL 1 year)) AND t2.status="Completed" AND t1.sector<>"Staff" THEN t2.id END) AS count
          FROM t2
          LEFT JOIN t1 ON t1.id = t2.id
          GROUP BY t2.id
          HAVING (count > 1)
         ) AS Volunteers;

因为显然你会收到错误Unknown columns 't2.recruitedby' in 'field list'因为我没有指定该列应该来自哪里 - 但如果我尝试添加“从底部的 t2"开始，我收到一条“SQL 语法错误”消息。我知道我可能必须再次使用 GROUP BY ——但我还没有做到这一点。

最后，理想情况下，我想向此查询添加更多列，以分解 t1 中的志愿者 - 因此，如果也可以包含 LEFT JOIN 来实现这一点，那太好了——但我想在不加入任何内容的情况下提出这个问题，这样我至少可以迈出第一步。

谢谢!

Answer 1

你需要子查询吗？ (由于可能出现重复名称，因此按 2 列分组)

SELECT t2.id, t2.recruitedby, COUNT(1) AS count
FROM t2
LEFT JOIN t1 ON t1.id = t2.id
WHERE
    STR_TO_DATE(t2.date,'%m/%d/%Y') > SUBDATE(now(), INTERVAL 1 year
    AND t2.status="Completed"
    AND t1.sector<>"Staff"
GROUP BY t2.id, t2.recruitedby
HAVING (count > 1)

编辑:上述命令返回不需要的结果。如果您的问题中的查询为志愿者返回了正确的结果，那么此命令是如何执行的？

SELECT t2.recruitedby, COUNT(Volunteers.count) AS Volunteers
FROM (
    SELECT t2.id, COUNT(CASE 
        WHEN (STR_TO_DATE(t2.date,'%m/%d/%Y') > SUBDATE(now(), INTERVAL 1 year))
        AND t2.status="Completed"
        AND t1.sector<>"Staff"
        THEN t2.id END) AS count
    FROM t2
    LEFT JOIN t1 ON t1.id = t2.id
    GROUP BY t2.id
    HAVING (count > 1)
   ) AS Volunteers
JOIN t2 ON t2.id=Volunteers.id

编辑:下面的查询(不太漂亮)返回这些行:

Smith, Jane    2
Smith, John    1

这是查询:

SELECT recruiters.recruitedby, COUNT(Volunteers.id) AS Volunteers
FROM
(
    SELECT t2.id, COUNT(CASE 
        WHEN (STR_TO_DATE(t2.date,'%m/%d/%Y') > SUBDATE(now(), INTERVAL 1 year))
        AND t2.status="Completed"
        AND t1.sector<>"Staff"
        THEN t2.id END) AS count
    FROM t2
    LEFT JOIN t1 ON t1.id = t2.id
    GROUP BY t2.id
    HAVING (count > 1)
) AS Volunteers
JOIN
(
    SELECT DISTINCT id, recruitedby
    FROM t2
) AS recruiters
ON recruiters.id=Volunteers.id
GROUP BY recruiters.recruitedby

我认为我从来没有写过像这样的查询!