MySQL LEFT JOIN搭配LIMIT 1未返回预期结果问题排查

嘿，我来帮你排查这个问题！你遇到的其实是新手常犯的JOIN条件逻辑错误，咱们一步步来分析：

问题根源 #

你原来的LEFT JOIN条件写得不对：

LEFT JOIN srvc_test ON lctn.testID = (SELECT srvc_test.testID FROM srvc_test WHERE srvc_test.testID = lctn.testID ORDER BY srvc_test.serviceDate DESC LIMIT 1)

这个子查询返回的结果永远等于当前行的lctn.testID（因为子查询里的WHERE srvc_test.testID = lctn.testID已经限定了只会返回和当前lctn行相同的testID），所以这个JOIN条件相当于lctn.testID = lctn.testID——永远为真！这就导致lctn_test的每一行都和srvc_test的所有行做了连接，也就是笛卡尔积，所以你得到了2*3=6行结果。

正确的实现方法 #

这里给你两种常用的解决方案，都能得到你想要的预期结果：

方法1：先分组取最大日期，再关联 #

先通过子查询找出每个testID对应的最新serviceDate，再关联回srvc_test获取完整的服务记录，最后和lctn_test连接：

SELECT lctn.testID AS assetID, lctn.ClientID, s.serviceDate, s.servicePassed
FROM lctn_test AS lctn
LEFT JOIN (
    -- 先筛选出每个testID的最新服务记录
    SELECT st.testID, st.serviceDate, st.servicePassed
    FROM srvc_test st
    INNER JOIN (
        -- 分组获取每个testID的最大服务日期
        SELECT testID, MAX(serviceDate) AS max_date
        FROM srvc_test
        GROUP BY testID
    ) st_max ON st.testID = st_max.testID AND st.serviceDate = st_max.max_date
) s ON lctn.testID = s.testID
WHERE lctn.ClientID = 34
ORDER BY assetID;

方法2：使用窗口函数（MySQL 8.0+适用） #

如果你用的是MySQL 8.0及以上版本，窗口函数会更简洁直观，用ROW_NUMBER()给每个testID的服务记录按日期倒序编号，然后取编号为1的那条（也就是最新的）：

SELECT assetID, ClientID, serviceDate, servicePassed
FROM (
    SELECT 
        lctn.testID AS assetID,
        lctn.ClientID,
        st.serviceDate,
        st.servicePassed,
        -- 按testID分组，每组内按serviceDate倒序编号
        ROW_NUMBER() OVER (PARTITION BY lctn.testID ORDER BY st.serviceDate DESC) AS row_num
    FROM lctn_test AS lctn
    LEFT JOIN srvc_test st ON lctn.testID = st.testID
    WHERE lctn.ClientID = 34
) AS temp
WHERE row_num = 1
ORDER BY assetID;

这两种方法都能正确返回每个asset的最新服务记录，而且如果某个asset没有服务记录（比如lctn_test里有个testID在srvc_test中不存在），LEFT JOIN会保留该行，对应的serviceDate和servicePassed字段会显示为NULL，符合你的需求。

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