mysql - sql 查询 : no payments in last 90 days

Question

假设我有一个有很多付款的客户。如何查询到最近90天内没有付款记录的所有客户？

clients
=======
id          integer
name        string

payments
========
id          integer
client_id   integer
created_at  datetime

本质上是相反的:

select *
from clients
inner join payments on payments.client_id = clients.id
where payments.created_at > utc_timestamp() - interval 90 day

希望比:

更有效率

select *
from clients 
where id not in (
  select *
  from clients
  inner join payments on payments.client_id = clients.id
  where payments.created_at > utc_timestamp() - interval 90 day
)

Answer 1

确保 payments(client_id) 上有一个索引，或者更好的是 payments(client_id, created_at)。

对于编写查询的替代方法，您可以尝试使用 not exists，例如:

select  *
from    clients c
where   not exists
        (
        select  *
        from    payments p
        where   p.payments.client_id = clients.id
                and payments.created_at > utc_timestamp() - interval 90 day
        )

或者独占左连接:

select  *
from    clients c
left join
        payments p
on      p.payments.client_id = clients.id
        and payments.created_at > utc_timestamp() - interval 90 day
where   p.client_id is null

如果两者都很慢，请将 explain extended 输出添加到您的问题中，以便我们了解原因。