MySQL按日期分组获取标题出现次数TOP5记录的实现方法

Got it, let's break this down step by step. To get the top 5 most frequent titles for each date, we'll use window functions (available in MySQL 8.0+)—they're perfect for ranking records within groups (in this case, groups of dates).

Step 1: Calculate Total Occurrences Per Date & Title #

First, we need to count how many times each title appears on each date. This is a basic grouped count:

SELECT 
    date,
    title,
    COUNT(*) AS total
FROM your_table
GROUP BY date, title

Step 2: Rank Titles Within Each Date #

Next, we'll wrap that count in a CTE (Common Table Expression) and add a ranking column. We have two options here depending on how you want to handle ties:

Option 1: Use ROW_NUMBER() (Strict Top 5, no ties)

If you want exactly 5 records per date (even if multiple titles have the same total count, only one gets the higher rank), use ROW_NUMBER():

WITH ranked_titles AS (
    SELECT 
        DATE_FORMAT(date, '%d %b') AS date, -- Format date like "20 Apr"
        title,
        COUNT(*) AS total,
        ROW_NUMBER() OVER (PARTITION BY date ORDER BY COUNT(*) DESC) AS rn
    FROM your_table
    GROUP BY date, title
)
SELECT date, title, total
FROM ranked_titles
WHERE rn <= 5
ORDER BY date, rn;

Option 2: Use RANK() (Include Ties)

If you want to include all titles that tie for the top positions (e.g., if two titles have the highest count, both get rank 1, and you might end up with more than 5 records for that date if there are ties), use RANK() instead:

WITH ranked_titles AS (
    SELECT 
        DATE_FORMAT(date, '%d %b') AS date, -- Format date like "20 Apr"
        title,
        COUNT(*) AS total,
        RANK() OVER (PARTITION BY date ORDER BY COUNT(*) DESC) AS rn
    FROM your_table
    GROUP BY date, title
)
SELECT date, title, total
FROM ranked_titles
WHERE rn <= 5
ORDER BY date, rn;

Key Notes: #

• Replace your_table with the actual name of your table.
• The DATE_FORMAT(date, '%d %b') part formats your date column to match the "20 Apr" style in your example. If your date field is already stored in this string format, you can skip this and just use the original date column.
• Window functions require MySQL 8.0 or later. If you're on an older version, we'd need a different approach using correlated subqueries, but window functions are far cleaner.

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