mysql - 如何在 MySQL 中使用 LEFT JOINed 表选择上一条记录？

Question

我正在努力让以下内容发挥作用:

# prev
SELECT klha.buyerID < PARAMETER_1

    FROM agents AS v

    LEFT JOIN seller_authorized AS klhs 
        ON klhs.agent= v.agentID 
        AND klhs.iln = v.seller
        AND v.`status` = 'auth'

    LEFT JOIN cust_list AS klha 
        ON klha.buyerID  = klhs.userID
        AND klha.accountNo = klhs.accountNo
        WHERE v.iln = PARAMETER_2
        AND klhs.acccountNo IS NOT NULL

    ORDER BY klha.buyerID 
    LIMIT 1 
;

结束

左连接正在工作，因为我在另一个过程中使用它们来选择所有相关记录。问题在于我尝试选择上一条记录。

我正在传递一个buyerID并且需要取回上一条记录的ID。

问题:
谁能指出我正确的语法？

编辑:
如果我在 MySQL 中运行它，我将得到“0”作为结果集

谢谢!

解决方案:
让它像这样工作:

# prev
SELECT klha.buyerID

    FROM agents AS v

    LEFT JOIN seller_authorized AS klhs 
        ON klhs.agent= v.agentID 
        AND klhs.iln = v.seller
        AND v.`status` = 'auth'

    LEFT JOIN cust_list AS klha 
        ON klha.buyerID  = klhs.userID
        AND klha.accountNo = klhs.accountNo
        WHERE v.iln = PARAMETER_2
        AND klhs.acccountNo IS NOT NULL
        AND klha.buyerID < PARAMETER_1

    ORDER BY klha.buyerID 
    LIMIT 1 
;

比想象的容易:-) 谢谢@Henrique Ordine

Answer 1

如果我正确理解您的需要，将此条件添加到您的 where 子句中应该可以解决问题:

并且 klha.buyerID =(从 BuyerID < PARAMETER_1 的代理商中选择 max(buyerID))

像这样:

SELECT klha.buyerID 

    FROM agents AS v

    LEFT JOIN seller_authorized AS klhs 
        ON klhs.agent= v.agentID 
        AND klhs.iln = v.seller
        AND v.`status` = 'auth'

    LEFT JOIN cust_list AS klha 
        ON klha.buyerID  = klhs.userID
        AND klha.accountNo = klhs.accountNo
    WHERE v.iln = PARAMETER_2
      AND klhs.acccountNo IS NOT NULL
      and klha.buyerID = (select max(buyerID) from agents 
                            where buyerID < PARAMETER_1)

    ORDER BY klha.buyerID 
    LIMIT 1