所以我有以下几点：

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子句计算同一组数据的计数。
我尝试了上述方法，因为它简化了一点查询，而不必在每个子查询中再次执行连接。但这似乎是不可能的，这是常见的表表达式，在mysql 5.7中不可用。
有什么更好的方法来实现这一点？
一定有比这个超长查询更干净的方法。
我也可以用一张临时的table，虽然我不确定这是个好主意。