mysql - 如何在子查询中使用 HAVING 作为 Select 语句的一部分

Question

我正在处理一个查询，以返回今年有多少事件家按团队不止一次地志愿服务；我让它作为一个独立的查询工作:

   SELECT Activists.team, COUNT(id) AS Activists
    FROM
    (
        SELECT e.id, v.team, 
        COUNT(CASE WHEN (e.status="Completed" AND right(e.date,2)="15") THEN e.id END) AS count
        FROM actiontable e
        LEFT JOIN persontable v ON v.id = e.id
        GROUP BY e.id, v.team
        HAVING (count > 1)
    ) AS Activists
    GROUP BY Activists.team;

但我不太清楚如何让它在更长的 SELECT 语句中工作。我遇到的问题是，在 SELECT 语句中，我还有许多其他(更简单的)查询部分要按团队返回内容，例如:

COUNT(DISTINCT CASE WHEN v.WillCanvass = "X" THEN v.id END)

很明显，我不能让 HAVING (count > 1) 部分查询事件家，因为那样它会影响我的 SELECT 的所有其他部分语句——所以我需要上面的子查询只影响我正在处理的唯一部分。

我做了一个SQL Fiddle使用示例架构来帮助上面的查询有效——但理想的是获得类似于此的输出，其中 Activists 子查询不影响 WillCanvass 列(即使我编造了下面的数字):

Team    Activists   WillCanvass
Team A  2           2
Team B  8           5
Team C  7           3

希望这是有道理的——谢谢!

---

编辑

我最好的结果是我想要的——尽管查询给了我错误——看起来像这样:

SELECT a.team as team,
    COUNT(v.*) as activists,
    SUM(CASE WHEN v.WillCanvass = "X" THEN 1 ELSE 0 END) as WillCanvass
FROM
persontable v
left join
(
    SELECT e.id, 
        v.team,
        v.WillCanvass,
        COUNT(*) as count
    FROM actiontable e
    LEFT JOIN persontable v ON v.id = e.id
    WHERE e.status="Completed" AND right(e.date,2)="15"
    GROUP BY e.id
    HAVING (count > 1)) as a 
        GROUP BY team;

有一个更新的 SQL Fiddle here .

Answer 1

不确定您要实现的目标。我首先获取具有正确标准的事件家列表，然后是 GROUP BY 团队。

SELECT a.team as team,
    COUNT(*) as activists,
    SUM(CASE WHEN a.WillCanvass = "X" THEN 1 ELSE 0 END) as WillCanvass
FROM (
    SELECT e.id, 
        v.team,
        v.WillCanvass,
        COUNT(*) as count
    FROM actiontable e
    LEFT JOIN persontable v ON v.id = e.id
    WHERE e.status="Completed" AND right(e.date,2)="15"
    GROUP BY e.id
    HAVING (count > 1)) as a 
GROUP BY team