mysql - 选择具有最高优先级的行

Question

我正在处理一个查询，但有点卡住了。

这是我的查询:

SELECT *
FROM 
    routine_actions AS ra
JOIN 
    routines AS r ON r.id = ra.routine_id
JOIN 
    account_routines AS ar ON ar.routine_id = r.id
JOIN 
    accounts AS a ON a.id = ar.account_id
WHERE
    (ra.last_run + INTERVAL ra.interval_minutes MINUTE <= NOW() OR ra.last_run IS NULL)
AND
    r.created_at + INTERVAL r.runtime_days DAY > NOW()

我正在尝试做的事情:

一个账号有很多套路。一次只能使用一个routine，即优先级最高的routine。包含优先级列的表是 account_routines，因为帐户可以重用例程并指定不同的优先级。

数字越大优先级越高。目前查询正在从所有帐户中提取所有例程。但我只需要每个帐户中一个具有最高优先级的例程。

这怎么可能？我不需要解决方案，只需要查找位置，以便弄清楚如何解决此问题。

Answer 1

我认为您需要找到 max(priority) 并将其包含在连接中以限制返回的行。例如

SELECT
      *
FROM routine_actions AS ra
      JOIN routines AS r ON r.id = ra.routine_id
      JOIN account_routines AS ar ON ar.routine_id = r.id
      JOIN (
            SELECT
                  account_id
                , MAX(priority) AS max_priority
            FROM account_routines
            GROUP BY
                  account_id
      ) AS mxr ON ar.priority = mxr.max_priority
                  AND ar.account_id = mxr.account_id
      JOIN accounts AS a ON a.id = ar.account_id
WHERE ...