mysql - MySQL 中的自连接计数

Question

我正在尝试做类似的事情

SELECT `v1`.`date`, COUNT(DISTINCT(`v2`.`id`)) AS `visits` 
FROM `visitor_visits` AS `v1` JOIN `visitor_visits` AS `v2` 
ON (`v1`.`date` = `v2`.`date`) GROUP BY `v1`.`date`, `v2`.`date` 
ORDER BY `v1`.`date

获取不同的日期以及所有其他匹配日期的计数。

我很确定这是错误的查询，因为日期列不唯一。

Answer 1

有什么问题:

SELECT v1.date, COUNT(v1.id) AS visits 
FROM visitor_visits AS v1
GROUP BY v1.date
ORDER BY v1.date

？

ps。我假设 id 是主键(唯一)。