mysql - 在另一个子查询中使用 From(子查询)表

Question

所以我有以下内容:

SELECT bt.CompanyName, 
    AcceptedTable.Count AS AcceptedCount,
    CompletedTable.Count AS CompletedCount,
    SkippedTable.Count AS SkippedCount,
    TotalTable.Count AS TotalCount
FROM (
    SELECT uic.*, uic.Id AS UCId, ic.*, ic.Id AS CompanyId, ic.Name AS CompanyName
    FROM UserChallenges iuc 
    JOIN Users iu ON iuc.UserId = iu.Id 
    JOIN Companies ic ON ic.Id = iu.CompanyId) bt
JOIN (
    SELECT CompanyId AS CompanyId, COUNT(UCId) AS Count
    FROM bt
    GROUP BY CompanyId) TotalTable ON CompletedTable.CompanyId = bt.CompanyId
JOIN (
    SELECT CompanyId AS CompanyId, COUNT(UCId) AS Count
    FROM bt
    WHERE AcceptedAt IS NOT NULL
    GROUP BY CompanyId) AcceptedTable ON CompletedTable.CompanyId = bt.CompanyId
JOIN (
    SELECT CompanyId AS CompanyId, COUNT(UCId) AS Count
    FROM bt
    WHERE DoneStatus IN (1,6)
    GROUP BY CompanyId) CompletedTable ON CompletedTable.CompanyId = bt.CompanyId
JOIN (
    SELECT CompanyId AS CompanyId, COUNT(UCId) AS Count
    FROM bt
    WHERE DoneStatus IN (4)
    GROUP BY CompanyId) SkippedTable ON SkippedTable.CompanyId = bt.CompanyId
GROUP BY bt.CompanyName

目标是计算具有不同 WHERE 子句的同一组数据的计数。

我尝试了上述方法，因为它稍微简化了查询，而不必在每个子查询中再次执行 JOIN。不过好像不行，是Common table expression并且在 mysql 5.7 中不可用。

什么是更好的实现方式？

必须有比这个超长查询更简洁的方法。

我也可以使用临时表，尽管我不确定在 View 中这样做是否是个好主意。

Answer 1

您不能推荐另一个 Derived Table不同派生表的 FROM 子句中的别名，因为它在该级别不可访问。

另外，我觉得你可以使用条件聚合来解决这个问题:

SELECT bt.CompanyName, 
       COUNT(CASE WHEN bt.AcceptedAt IS NOT NULL THEN bt.UCId END) AS AcceptedCount,
       COUNT(CASE WHEN bt.DoneStatus IN (1,6) THEN bt.UCId END) AS CompletedCount,
       COUNT(CASE WHEN bt.DoneStatus IN (4) THEN bt.UCId END) AS SkippedCount,
       COUNT(bt.UCId) AS TotalCount
FROM (
    SELECT uic.*, 
           uic.Id AS UCId, 
           ic.*, 
           ic.Id AS CompanyId, 
           ic.Name AS CompanyName
    FROM UserChallenges iuc 
    JOIN Users iu ON iuc.UserId = iu.Id 
    JOIN Companies ic ON ic.Id = iu.CompanyId) bt
GROUP BY bt.CompanyId, bt.CompanyName