使用BETWEEN与OR的性能对比：日期匹配关联查询最优方案咨询

兄弟，咱们来捋捋你这个需求和两个方案的问题，再给你个靠谱的最优解！

首先明确你的核心需求：每条#table1的记录，关联#exchange表时优先取和ordertime日期一致的数据；如果当日没有匹配数据，就取前一天的。先看你写的两个方案：

先揪出逻辑硬伤！ #

你写的方案二逻辑完全错误！BETWEEN的左右边界搞反了：#table1.ordertime::date BETWEEN A.begin::date AND dateadd(d,-1, A.begin)::date——比如A.begin是2024-05-20，那右边就是2024-05-19，这时候BETWEEN等价于「2024-05-20 到 2024-05-19」，SQL里左值大于右值的BETWEEN不会返回任何结果，等于白写！

再看方案一的OR：#table1.ordertime::date = A.begin::date OR #table1.ordertime::date = dateadd(d,-1, A.begin)::date——这里你大概率把字段写反了吧？应该是A.begin的日期等于ordertime的日期，或者A.begin等于ordertime的前一天？就算逻辑写对了，这个OR也会导致一个问题：如果某条id同时有当日和前一日的exchange数据，查询会返回两条重复记录，最后#table2里会出现冗余数据，这显然不是你要的「优先取当日」的效果。

性能对比：OR vs 正确的BETWEEN #

假设咱们把逻辑修正过来，比如正确的范围条件是A.begin::date BETWEEN dateadd(d,-1, #table1.ordertime)::date AND #table1.ordertime::date（匹配前一天到当天），那：

• 用OR的话，数据库优化器对OR条件的处理经常拉胯，就算你有(source, begin)的复合索引，也可能触发全表扫描，性能堪忧；
• 用BETWEEN做范围查询，只要有(source, begin)的复合索引，数据库可以直接走索引范围扫描，性能比OR好很多——范围扫描是索引友好的操作，优化器能精准识别。

但不管是OR还是BETWEEN，都解决不了「优先取当日」的核心需求——它们都会把两天的数据查出来，你得额外做去重和优先级筛选。

最优实现：窗口函数+范围查询 #

给你写个逻辑正确、性能拉满的方案：

INSERT INTO #table2
SELECT id, ordertime, current
FROM (
    SELECT 
        t1.id, 
        t1.ordertime, 
        ex.current,
        -- 给匹配记录排序：当日数据排第1，前一日排第2
        ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY 
            CASE WHEN ex.begin::date = t1.ordertime::date THEN 1 ELSE 2 END) AS rn
    FROM #table1 t1
    LEFT JOIN #exchange ex 
        ON t1.id = ex.source 
        -- 精准匹配前一天到当天的exchange数据
        AND ex.begin::date BETWEEN dateadd(d, -1, t1.ordertime)::date AND t1.ordertime::date
) ranked
-- 只取每个id的第一条记录（优先当日）
WHERE rn = 1;

这个方案的优势：

1. 用BETWEEN做范围查询，利用快速索引筛选出目标数据；
2. 用ROW_NUMBER()给每个id的匹配记录排序，确保当日数据排在最前面；
3. 最后只取rn=1的记录，完美实现「有当日取当日，无则取前一日」的需求，还不会产生重复数据。

总结一下 #

• 原始方案二逻辑错误，先把边界顺序修正过来；
• 单纯的OR方案会返回重复数据，性能也不如范围查询；
• 结合BETWEEN范围查询+窗口函数排序，才是逻辑正确、性能最优的最终方案。

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