所以我有以下几点:
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,虽然我不确定这是个好主意。
1条答案
按热度按时间bmp9r5qi1#
不能在其他派生表的别名中引用其他派生表别名
FROM
子句,因为它在该级别不可访问。另外,我觉得您可以使用条件聚合来解决这个问题: