如何在关联subscribers与payments表时新增last_update列（取两表updated_at的最大值）

根据你的场景，我提供两种实用的查询写法，既能满足你获取两张表updated_at最大值的需求，还能处理订阅者没有支付记录的边界情况：

方案一：直接关联后分组计算（适用于小数据量场景） #

如果你用的是类似CakePHP的查询构建器，可以这样修改你的现有查询：

$query->select([
        "$this->subscribersTable.id",
        "$this->subscribersTable.updated_at",
        'last_update' => $query->func()->max(
            $query->func()->greatest(
                "$this->subscribersTable.updated_at",
                $query->func()->coalesce("$this->paymentsTable.updated_at", '1970-01-01')
            )
        )
    ])
    ->leftJoin($this->paymentsTable, "$this->paymentsTable.subscriber_id = $this->subscribersTable.id")
    ->group("$this->subscribersTable.id");

对应的原生SQL版本：

SELECT 
    s.id,
    s.updated_at,
    MAX(GREATEST(s.updated_at, COALESCE(p.updated_at, '1970-01-01'))) AS last_update
FROM subscribers s
LEFT JOIN payments p ON p.subscriber_id = s.id
GROUP BY s.id;

逻辑说明： #

• GREATEST(s.updated_at, p.updated_at)：直接对比订阅者和对应支付记录的更新时间，取较大值；
• COALESCE(p.updated_at, '1970-01-01')：处理订阅者无支付记录的情况，把NULL替换成一个极早的时间，确保GREATEST最终取订阅者自身的updated_at；
• MAX(...)：因为一个订阅者可能有多条支付记录，通过分组后取最大值，得到该订阅者的最新更新时间。

方案二：子查询预取最新支付时间（适用于大数据量场景） #

如果你的payments表数据量很大，先通过子查询预取每个订阅者的最新支付时间，再关联计算会更高效：

查询构建器写法（CakePHP为例） #

// 先构建子查询，获取每个订阅者的最新支付时间
$latestPaymentSubquery = $this->paymentsTable->find()
    ->select([
        'subscriber_id',
        'latest_payment' => $this->paymentsTable->query()->func()->max('updated_at')
    ])
    ->group('subscriber_id');

// 主查询关联子查询，计算last_update
$query->select([
        "$this->subscribersTable.id",
        "$this->subscribersTable.updated_at",
        'last_update' => $query->func()->greatest(
            "$this->subscribersTable.updated_at",
            $query->func()->coalesce('lp.latest_payment', "$this->subscribersTable.updated_at")
        )
    ])
    ->leftJoin(
        ['lp' => $latestPaymentSubquery],
        'lp.subscriber_id = ' . $this->subscribersTable . '.id'
    );

原生SQL版本 #

SELECT 
    s.id,
    s.updated_at,
    GREATEST(s.updated_at, COALESCE(lp.latest_payment, s.updated_at)) AS last_update
FROM subscribers s
LEFT JOIN (
    SELECT subscriber_id, MAX(updated_at) AS latest_payment
    FROM payments
    GROUP BY subscriber_id
) lp ON lp.subscriber_id = s.id;

逻辑说明： #

• 子查询先对payments按subscriber_id分组，提前算出每个订阅者的最新支付时间，减少主查询的关联数据量；
• 主查询中直接对比订阅者自身的updated_at和预取的最新支付时间，用COALESCE处理无支付记录的情况，无需额外分组。

用你的示例数据测试，两种方案都会得到你期望的结果：

• 订阅者1的last_update为2022-02-02
• 订阅者2的last_update为2021-02-02

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