php - 两个 SQL 查询返回奇怪的结果

Question

我将运行一个查询，该查询在提供 admission_code 时提供最新的欠款信息，但存在一些逻辑错误:

SELECT * 
FROM fees_invoice_information AS fii
WHERE fii.admission_code = 12111

此 SQL 返回:

enter image description here

当我想从以下查询中获取最新记录时

SELECT
    MAX(fii.id),fii.*,
    sep.registration_code,
    sep.admission_code,
    fii.arrears,
    sep.is_enabled 
FROM 
    fees_invoice_information AS fii
JOIN 
    std_education_profile AS sep ON fii.admission_code = sep.admission_code 
WHERE 
    fii.`admission_code`  = 12111

我得到:

enter image description here

其中“欠费”栏不为“0”

Answer 1

将 MAX 条件移至 WHERE 子句中的子查询:

SELECT fii.*,
       sep.registration_code,
       sep.admission_code,
       sep.is_enabled 
FROM fees_invoice_information AS fii
INNER JOIN std_education_profile AS sep
    ON fii.admission_code = sep.admission_code 
WHERE fii.admission_code = 12111 AND
      fii.id = (SELECT MAX(id) FROM fees_invoice_information)

或者你可以使用LIMIT 1技巧:

SELECT fii.*,
       sep.registration_code,
       sep.admission_code,
       sep.is_enabled 
FROM fees_invoice_information AS fii
INNER JOIN std_education_profile AS sep
    ON fii.admission_code = sep.admission_code 
WHERE fii.admission_code = 12111
ORDER BY fii.id DESC
LIMIT 1

注意:这假设根据最大 ID 仅返回单个记录是合乎逻辑的。如果联接不是一对一的，那么您可能希望返回一组记录或一组记录的聚合。