mysql - 为数据库中的每个债务人/客户选择 MAX(DATEDIFF())

Question

我有以下查询适用于我的研究目的(针对单个债务人):

SELECT MAX(DATEDIFF(r.next_created, r.created))
    FROM (

    # finds each created and the next consecutive one in the table
    SELECT r1.created as created, (

        # finds the next consecutive created
        SELECT created
        FROM (db) r2
        WHERE r2.created > r1.created
        ORDER BY created ASC
        LIMIT 1
    ) as next_created

    FROM (db) r1
    ORDER BY r1.created) as r

但是，我非常希望能够将其扩展到数据库中的每个 Debor_id。你们对我如何做到这一点有什么意见吗？

提前致谢。

Answer 1

SELECT r.debtor, MAX(DATEDIFF(r.next_created, r.created))
FROM (SELECT r1.*
             (SELECT r2.created
              FROM db r2
              WHERE r2.debtor = r1.debtor AND r2.created > r1.created
              ORDER BY r2.created ASC
              LIMIT 1
             ) as next_created
      FROM db r1
     ) r
GROUP BY r.debtor;

这假设您的表有一列名为debtor(或其他名称)的列，用于标识您关心的内容。