如何高效实现跨表关联日期条件下的SQL聚合查询？

针对你的场景，以下几种SQL实现方式比关联子查询更高效，配合索引优化可进一步提升性能：

1. 关联后聚合（提前过滤日期） #

先通过CAR_ID关联两张表，同时过滤出符合日期范围的付款记录，再按车辆分组统计。这种方式避免子查询嵌套，让数据库优化器更易生成高效执行计划：

SELECT 
    c.CAR_ID,
    COUNT(p.Car_Payment_Amt) AS payment_count
FROM car c
JOIN car_payments p 
    ON c.CAR_ID = p.CAR_ID
    AND p.Car_Payment_DT BETWEEN c.Car_START_DT AND c.Car_END_DT
GROUP BY c.CAR_ID;

若需保留所有车辆（包括无符合条件付款记录的），将JOIN替换为LEFT JOIN，并调整统计逻辑确保无记录时显示0：

SELECT 
    c.CAR_ID,
    SUM(CASE 
        WHEN p.Car_Payment_DT BETWEEN c.Car_START_DT AND c.Car_END_DT THEN 1 
        ELSE 0 
    END) AS payment_count
FROM car c
LEFT JOIN car_payments p 
    ON c.CAR_ID = p.CAR_ID
GROUP BY c.CAR_ID;

2. 相关子查询（配合索引优化） #

直接在car表上通过相关子查询统计，若给car_payments建立合适索引，性能会远优于你之前的分组子查询：

SELECT 
    c.CAR_ID,
    (SELECT COUNT(*) 
     FROM car_payments p 
     WHERE p.CAR_ID = c.CAR_ID
       AND p.Car_Payment_DT BETWEEN c.Car_START_DT AND c.Car_END_DT) AS payment_count
FROM car c;

关键索引优化 #

性能提升的核心在于索引，建议创建以下索引：

• 给car_payments表创建复合索引：CREATE INDEX idx_car_payments_cid_dt ON car_payments(CAR_ID, Car_Payment_DT); 可快速定位指定车辆、指定日期范围内的付款记录，避免全表扫描。
• 若car表的CAR_ID非主键，给其创建主键或唯一索引，加快关联速度。

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