SQL查询问题：如何按唯一日期统计对应访问量？

The problem with your original query when adding COUNT(action) is that without a GROUP BY clause, the database aggregates all matching rows into a single result—hence why you only get one date and the total hits. Here's how to adjust it to get per-date counts:

SELECT 
    FROM_UNIXTIME(timecreated, '%Y-%m-%d') AS `date`,
    COUNT(action) AS `hits`
FROM `mdl_logstore_standard_log`
WHERE action = 'viewed'
GROUP BY `date`
ORDER BY `date` ASC;

Key Changes Explained: #

• **GROUP BY \date`**: This tells the database to group all rows by the formatted date value. The COUNT(action)` will then calculate the number of entries for each unique date instead of the total across all dates.
• Removed DISTINCT: GROUP BY already ensures we only get unique dates, so DISTINCT is redundant here.
• ORDER BY \date` ASC`: Optional but recommended—this sorts the results in chronological order, making the hit trends easier to read.

If you prefer your original date format (dd.mm.yy), just update the FROM_UNIXTIME format string to '%d.%m.%y' instead of '%Y-%m-%d'.

内容的提问来源于stack exchange，提问作者baumi_