SQL中MAX函数内使用条件查询乐队最新已举办演出日期问题

• 你最初写的max(date <= '2021-11-24')逻辑错误：date <= 参考日期返回的是布尔判断结果，不是日期值，max计算布尔值只能得到1/0或者true/false，无法得到实际的最近演出日期
• 现有查询没有添加日期过滤条件，所以max(date)会统计该乐队所有演出的最大日期，包含未来场次
• 现有查询缺少GROUP BY子句，同时直接聚合max(date)的情况下，选中的venue等非聚合字段无法保证和最近演出日期对应，会出现匹配错误的问题
• 额外注意：你原SQL中max(date) as lastShowDate, FROM多了一个多余的逗号，会触发语法错误，修正时需要去掉

参考日期统一使用YYYY-MM-DD标准格式避免不同数据库的解析异常，示例参考日期为'2021-11-24'，可根据需求替换为变量。

写法1：支持窗口函数的数据库（MySQL8.0+/PostgreSQL/SQL Server等，最准确） #

可以正确匹配最近演出对应的场地信息：

SELECT 
    b.bandId, 
    b.bandGuid, 
    b.bandName, 
    e.venue AS lastVenue, 
    e.venueGuid AS lastVenueGuid,
    e.date AS lastShowDate
FROM band AS b
LEFT JOIN eventsBand eb ON eb.bandId = b.bandId
LEFT JOIN events e ON e.eventId = eb.eventId 
    AND e.date <= '2021-11-24' -- 仅关联参考日期前的演出
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY b.bandId 
    ORDER BY e.date DESC
) = 1 -- 取每个乐队最新的那场演出

如果数据库不支持QUALIFY语法，可以改用子查询实现：

SELECT 
    t.bandId, 
    t.bandGuid, 
    t.bandName, 
    t.venue AS lastVenue, 
    t.venueGuid AS lastVenueGuid,
    t.date AS lastShowDate
FROM (
    SELECT 
        b.bandId, 
        b.bandGuid, 
        b.bandName, 
        e.venue, 
        e.venueGuid,
        e.date,
        ROW_NUMBER() OVER (PARTITION BY b.bandId ORDER BY e.date DESC) AS rn
    FROM band AS b
    LEFT JOIN eventsBand eb ON eb.bandId = b.bandId
    LEFT JOIN events e ON e.eventId = eb.eventId AND e.date <= '2021-11-24'
) t
WHERE t.rn = 1

写法2：不支持窗口函数的低版本数据库 #

SELECT 
    b.bandId, 
    b.bandGuid, 
    b.bandName, 
    e.venue AS lastVenue, 
    e.venueGuid AS lastVenueGuid,
    t.lastShowDate
FROM band AS b
LEFT JOIN (
    SELECT eb.bandId, MAX(e.date) AS lastShowDate
    FROM eventsBand eb
    JOIN events e ON e.eventId = eb.eventId AND e.date <= '2021-11-24'
    GROUP BY eb.bandId
) t ON b.bandId = t.bandId
LEFT JOIN eventsBand eb ON eb.bandId = b.bandId
LEFT JOIN events e ON e.eventId = eb.eventId AND e.date = t.lastShowDate

可选调整说明 #

如果需要过滤掉参考日期前没有演出记录的乐队，把对应LEFT JOIN改成INNER JOIN即可。

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