如何编写子查询统计当前事件日期后的剩余日期行数？

错误原因分析 #

你之前的子查询中使用ed3.event_date > ed3.event_date属于逻辑错误——同一行的日期与自身比较永远不成立，导致剩余日期数统计始终为0。需要改为统计同一事件中，日期晚于当前行日期的记录数量。

推荐SQL写法（窗口函数实现，高效简洁） #

MariaDB 10.3支持窗口函数，用以下语句可以直接实现需求：

SELECT 
    e.event_name,
    DATE_FORMAT(ed.event_date, '%d/%m/%Y') AS event_date,
    CONCAT(total_shows, ' shows in total') AS total,
    CONCAT(row_num - 1, ' remaining after this') AS remaining
FROM (
    SELECT 
        event_id,
        event_date,
        -- 统计当前事件的总日期数
        COUNT(*) OVER(PARTITION BY event_id) AS total_shows,
        -- 按日期倒序给同一事件的日期编号，编号减1即为剩余日期数
        ROW_NUMBER() OVER(PARTITION BY event_id ORDER BY event_date DESC) AS row_num
    FROM event_dates
) ed_stats
JOIN events e ON ed_stats.event_id = e.id
ORDER BY e.event_name, ed_stats.event_date;

原理说明 #

1. 内层子查询通过COUNT(*) OVER(PARTITION BY event_id)统计每个事件的总日期数；
2. ROW_NUMBER() OVER(PARTITION BY event_id ORDER BY event_date DESC)给同一事件的日期按从晚到早的顺序编号：最新日期编号为1，次新为2，以此类推；
3. 编号减1就是当前日期之后的剩余日期数（最新日期剩余0，次新剩余1，完全匹配你的示例需求）；
4. 最后关联events表获取事件名称，并用DATE_FORMAT格式化日期输出。

备选方案（关联子查询实现） #

如果更习惯子查询写法，也可以用以下语句：

SELECT 
    e.event_name,
    DATE_FORMAT(ed.event_date, '%d/%m/%Y') AS event_date,
    CONCAT(total.total_shows, ' shows in total') AS total,
    CONCAT(remaining.remaining_shows, ' remaining after this') AS remaining
FROM events e
JOIN event_dates ed ON e.id = ed.event_id
-- 关联统计总日期数的子查询
JOIN (
    SELECT event_id, COUNT(*) AS total_shows
    FROM event_dates
    GROUP BY event_id
) total ON ed.event_id = total.event_id
-- 关联统计剩余日期数的子查询
LEFT JOIN (
    SELECT 
        ed1.event_id,
        ed1.event_date,
        COUNT(ed2.event_date) AS remaining_shows
    FROM event_dates ed1
    LEFT JOIN event_dates ed2 
        ON ed1.event_id = ed2.event_id 
        AND ed2.event_date > ed1.event_date
    GROUP BY ed1.event_id, ed1.event_date
) remaining ON ed.event_id = remaining.event_id AND ed.event_date = remaining.event_date
ORDER BY e.event_name, ed.event_date;

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