mysql - MySQL-按分组产生重复的行

给定以下数据：

CREATE TABLE Bugs
    (`ID` int, `BugDate` DATE, `resolved` int)
;

INSERT INTO Bugs
    (`ID`, `BugDate`, `Resolved`)
VALUES
    (1, '2014-01-01', 1),
    (2, '2014-01-01', 0),
    (3, '2014-01-10', 1),
    (4, '2014-01-10', 1),
    (5, '2014-01-31', 0),
    (6, '2014-01-31', 0)
;

我需要如下输出：

weeks old  Resolved  Unresolved  total  Rate
---------  --------  ----------  -----  ----
    1        1            1        2     50%
    2        2            0        2    100%
    3        0            2        0    100%

我正在查看问题列表，我想查看每周的总数和每周解决的数量。我已经接受了，我可能需要计算前端的total和rate列，而不是让MySQL执行。

我已经尝试了以下方法，但还没有达到目标：

SELECT
  WEEKOFYEAR(NOW())-WEEKOFYEAR(BugDate) as WeeksOld,
  COUNT(Resolved) as NumberResolved,
  ( /* get the total number of issues for this time period */
    SELECT COUNT(Resolved)
    FROM Bugs
    WHERE WEEKOFYEAR(NOW())-WEEKOFYEAR(BugDate) = WeeksOld
  ) - COUNT(Resolved) as Unresolved
FROM Bugs
GROUP BY WEEKOFYEAR(NOW())-WEEKOFYEAR(BugDate), Resolved
ORDER BY WeeksOld DESC;

我很亲近，但问题是，我有两周都出现了双列，其中既有已解决的问题也有未解决的问题。

我想念什么？有一个更好的方法吗？

这是一个小提琴：http://sqlfiddle.com/#!2/eeb34/12

最佳答案

您只需要条件聚合：

SELECT WEEKOFYEAR(NOW())-WEEKOFYEAR(BugDate) as WeeksOld,
       SUM(Resolved) as NumberResolved,
       SUM(1 - Resolved) as Unresolved,
       concat(format(AVG(Resolved)*100, 1), '%') as Rate
FROM Bugs
GROUP BY WEEKOFYEAR(NOW())-WEEKOFYEAR(BugDate)
ORDER BY WeeksOld DESC;

请注意，这将从Resolved中删除group by。它将resolved的计算更改为使用sum()而不是count()。它添加Rate列。并更改Unresolved的计算。您可以将后者写为：

count(*) - sum(Resolved)