SQL查找到另一个表进行聚合并将其合并回外部查询

Question

查询:能够从表A查找到表B，并使用基于日期标准的聚合函数引用表A的日期字段和表B的日期字段。

场景:

我有一个汽车表(包含CAR_ID,Car_START_DT,Car_END_DT)和一个car_payments表(包含CAR_ID, Car_Payment_DT, Car_Payment_Amt)。

对于car表中的每一辆车，我想使用CAR_ID对car_payments表进行查找，并通过使用Car_Payment_DT计算Car_START_DT和Car_END_DT(来自car表)之间的Car_Payment_Amt记录的数量来进行汇总。

对于我的尝试，我在car_payments表下创建了一个子查询COUNT(Car_Payment_Amt) GROUP BY CAR_ID，并根据CAR_ID将其与Car表连接以获得结果，但意识到随着数据大小的增长，子查询将花费比预期更长的时间。

我如何使用SQL有效地做到这一点?我做了一个搜索，人们说使用关联查询，但它有性能瓶颈。还有其他选择吗?

Answer 1

使用一个简单的连接

select
c.car_id, count(cp.payment_amt) as pmt_count
from
car c 
left join
car_payment cp on cp.car_id = c.car_id 
and cp.payment_dt between c.car_start_dt and c.car_end_date
group by
c.car_id

Answer 2

您可以尝试不分组，像这样:

SELECT  c.CAR_ID, 
(Select Count(*) From payments 
Where CAR_ID = c.CAR_ID And PAY_DATE Between c.START_DATE And c.END_DATE) "NO_OF_PAYS"
FROM    cars c

包含以下示例数据:

WITH
cars (CAR_ID, START_DATE, END_DATE) AS
(
Select 1, To_Date('01.01.2023', 'dd.mm.yyyy'), To_Date('04.01.2023', 'dd.mm.yyyy') From Dual Union All
Select 2, To_Date('01.01.2023', 'dd.mm.yyyy'), To_Date('06.01.2023', 'dd.mm.yyyy') From Dual Union All
Select 3, To_Date('03.01.2023', 'dd.mm.yyyy'), To_Date('08.01.2023', 'dd.mm.yyyy') From Dual Union All
Select 4, To_Date('03.01.2023', 'dd.mm.yyyy'), To_Date('10.01.2023', 'dd.mm.yyyy') From Dual Union All
Select 5, To_Date('05.01.2023', 'dd.mm.yyyy'), To_Date('12.01.2023', 'dd.mm.yyyy') From Dual Union All
Select 6, To_Date('07.01.2023', 'dd.mm.yyyy'), To_Date('14.01.2023', 'dd.mm.yyyy') From Dual 
),
payments (CAR_ID, PAY_DATE, AMAUNT) AS
(
Select 1, To_Date('01.01.2023', 'dd.mm.yyyy'), 100 From Dual Union All
Select 1, To_Date('03.01.2023', 'dd.mm.yyyy'), 100 From Dual Union All
Select 1, To_Date('05.01.2023', 'dd.mm.yyyy'), 100 From Dual Union All
Select 1, To_Date('06.01.2023', 'dd.mm.yyyy'), 100 From Dual Union All
Select 3, To_Date('05.01.2023', 'dd.mm.yyyy'), 300 From Dual Union All
Select 3, To_Date('08.01.2023', 'dd.mm.yyyy'), 300 From Dual Union All
Select 3, To_Date('11.01.2023', 'dd.mm.yyyy'), 300 From Dual Union All
Select 5, To_Date('11.01.2023', 'dd.mm.yyyy'), 500 From Dual Union All
Select 5, To_Date('13.01.2023', 'dd.mm.yyyy'), 500 From Dual Union All
Select 6, To_Date('06.01.2023', 'dd.mm.yyyy'), 600 From Dual Union All
Select 6, To_Date('12.01.2023', 'dd.mm.yyyy'), 600 From Dual 
)

…结果

--  R e s u l t :
--      CAR_ID NO_OF_PAYS
--  ---------- ----------
--           1          2 
--           2          0 
--           3          2 
--           4          0 
--           5          1 
--           6          1