我有一个表，用于存储产品（product_versions）的版本。这些产品版本
可以具有一组状态（active，draft，archived），我需要获取每天（历史上）这些状态的合计结果集，以便可以生成图表。
虽然我知道我可以使用应用程序代码（PHP）或数据库触发器来构建和维护聚合表，但我希望在查询中解决此问题（部分是因为我希望它很优雅，部分是因为我对此感到好奇怎么做）。如果可以实现，则可以缓存结果或类似结果。
到目前为止，我能够获得像这样的单个状态的结果：
SELECT
pv.created_at_date,
(
SELECT CONCATcount(*)
FROM `product_versions` p
JOIN (
SELECT product_id, MAX(id) AS latest_version
FROM product_versions
GROUP BY product_id
) grouped_versions ON p.product_id = grouped_versions.product_id AND p.id = grouped_versions.latest_version
WHERE created_at_date = pv.created_at_date
AND status = 'draft'
ORDER BY id ASC
) as draft_status_count_subquery,
FROM product_versions pv
WHERE created_at_date >= date_sub(now(), interval 7 day)
GROUP BY pv.created_at_date
ORDER BY pv.created_at_date desc
结果：
+-----------------+-----------------------------+
| created_at_date | draft_status_count_subquery |
+-----------------+-----------------------------+
| 2013-09-09 | 0 |
| 2013-09-06 | 26 |
| 2013-09-05 | 40 |
| 2013-09-04 | 46 |
+-----------------+-----------------------------+
重要的是要注意，我需要能够确定每天的最新版本状态（使用grouped_versions子查询），以便返回正确的聚合。
我所有其他尝试加入其他状态的尝试均无效（除了显而易见的是，我不愿意为每个状态添加其他子查询（status_count_subquery 1 .. n），因为性能已经很慢了1个子查询）。
因此，我问外面的世界，有没有更好的方法来达到这个结果（或类似的结果）？
测试数据
以下是一些重新创建场景的示例数据：
CREATE TABLE `product_versions` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned DEFAULT NULL,
`created_at_date` date DEFAULT NULL,
`title` varchar(100) DEFAULT NULL,
`status` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `product_versions` (`id`, `product_id`, `created_at_date`, `title`, `status`)
VALUES
(1, 1, '2013-09-06', 'Foo Product', 'draft'),
(2, 1, '2013-09-06', 'Foo Product', 'active'),
(3, 2, '2013-09-06', 'Bar Product', 'draft'),
(4, 1, '2013-09-07', 'Foo Product', 'archived'),
(5, 2, '2013-09-07', 'Bar Product', 'active'),
(6, 3, '2013-09-07', 'Baz Product', 'draft'),
(7, 4, '2013-09-07', 'Fiz Product', 'draft');
希望这样的输出
+-----------------+-------+--------+----------+
| created_at_date | draft | active | archived |
+-----------------+-------+--------+----------+
| 2013-09-07 | 2 | 1 | 1 |
| 2013-09-06 | 1 | 1 | 0 |
+-----------------+-------+--------+----------+
最佳答案
由于@Goat CO的建议，将它钉了（是的！）：
SELECT
p.created_at_date,
SUM(status = 'draft') as draft,
SUM(status = 'active') as active,
SUM(status = 'archived') as archived
FROM `product_versions` p
JOIN (
SELECT product_id, MAX(id) AS latest_version
FROM product_versions
GROUP BY created_at_date, product_id
) grouped_versions ON p.product_id = grouped_versions.product_id AND p.id = grouped_versions.latest_version
GROUP BY created_at_date
ORDER BY created_at_date DESC
结果
+-----------------+-------+--------+----------+
| created_at_date | draft | active | archived |
+-----------------+-------+--------+----------+
| 2013-09-07 | 2 | 1 | 1 |
| 2013-09-06 | 1 | 1 | 0 |
+-----------------+-------+--------+----------+
让我知道是否还有其他值得研究的解决方案，或者该解决方案中是否有错误。