SQL查询如何实现优先按RELEASE_DATE筛选、无匹配时使用PRODUCTION_DATE

实现思路 #

要实现优先按RELEASE_DATE筛选、无对应记录时降级到PRODUCTION_DATE的逻辑，核心是给每个产品的product_time记录按类型设置优先级，取每个产品优先级最高且符合时间范围的记录关联查询即可。

方案1：窗口函数实现（兼容MySQL 8.0+、PostgreSQL、Oracle等主流数据库） #

这是最简洁易维护的写法：

SELECT p.*
FROM product p
INNER JOIN (
    SELECT 
        pt.*,
        -- 给同产品的记录按类型优先级排序
        ROW_NUMBER() OVER (
            PARTITION BY pt.product_id 
            ORDER BY CASE pt.type 
                WHEN 'RELEASE_DATE' THEN 1 
                WHEN 'PRODUCTION_DATE' THEN 2 
            END
        ) AS priority_rank
    FROM product_time pt
    WHERE pt.time BETWEEN ? AND ?
      AND pt.type IN ('RELEASE_DATE', 'PRODUCTION_DATE')
) pt_filtered ON p.id = pt_filtered.product_id AND pt_filtered.priority_rank = 1;

逻辑说明：子查询会筛选出所有时间符合要求的目标类型记录，给每个产品的记录排序，RELEASE_DATE优先级更高排第1，最终只取每个产品排名第1的记录关联产品表。

方案2：低版本MySQL兼容实现（不支持窗口函数场景） #

如果使用的是MySQL 5.x等不支持窗口函数的数据库，可以用左关联判断的方式实现：

SELECT DISTINCT p.*
FROM product p
INNER JOIN product_time pt ON p.id = pt.product_id
-- 先判断当前产品是否存在符合时间要求的RELEASE_DATE记录
LEFT JOIN product_time pt_release 
    ON pt.product_id = pt_release.product_id 
    AND pt_release.type = 'RELEASE_DATE'
    AND pt_release.time BETWEEN ? AND ?
WHERE pt.time BETWEEN ? AND ?
AND (
    -- 有RELEASE_DATE记录时就只取RELEASE_DATE的
    (pt_release.id IS NOT NULL AND pt.type = 'RELEASE_DATE')
    -- 没有时就取PRODUCTION_DATE的
    OR (pt_release.id IS NULL AND pt.type = 'PRODUCTION_DATE')
);

注意：该方案中两个BETWEEN ? AND ?需要传入相同的起止时间参数。

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