限制为1的mysql左联接未返回所需结果

umuewwlo 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(460)

我在sql查询方面遇到问题；我正在尝试获取每个“资产”的最后服务日期。
我正在尝试将左连接与限制为1的子查询一起使用。
这是我的table：
lctn\U试验

testID, cleintID
1,      34
2,      34

srvc\U测试

srvcTestID, testID, serviceDate,            servicePassed
1,          1,      2018-05-19 03:23:53,    1
2,          1,      2018-05-19 11:46:49,    1
3,          2,      2018-05-19 11:47:24,    1

以下是我尝试过的（以及一些变体）

SELECT 
    lctn.testID AS assetID, lctn.ClientID, 
    srvc_test.serviceDate, srvc_test.servicePassed
FROM 
    lctn_test AS lctn
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)
WHERE 
    lctn.ClientID = 34
ORDER BY 
    assetID

我期望得到：

assetID,    ClientID,   serviceDate,            servicePassed
1,          34,         2018-05-19 11:46:49,    1
2,          34,         2018-05-19 11:47:24,    1

但实际上我得到的是：

assetID,    ClientID,   serviceDate,            servicePassed
1,          34,         2018-05-19 03:23:53,    1
1,          34,         2018-05-19 11:46:49,    1
1,          34,         2018-05-19 11:47:24,    1
2,          34,         2018-05-19 03:23:53,    1
2,          34,         2018-05-19 11:46:49,    1
2,          34,         2018-05-19 11:47:24,    1

我仍然在学习sql（mysql），就我的一生而言，我看不出这个问题；我打赌这是一个错误，但我只是不明白。

sql mysql subquery left-join

来源：https://stackoverflow.com/questions/50425293/mysql-left-join-with-limit-1-not-returning-desired-result

3条答案

按热度按时间

y1aodyip1#

您的查询缺少实际的 join 条件：

SELECT lctn.testID AS assetID, lctn.ClientID, srvc_test.serviceDate, srvc_test.servicePassed
FROM lctn_test lctn LEFT JOIN
     srvc_test st
     ON st.testID = lctn.testID AND
        st.testID = (SELECT st2.testID
                     FROM srvc_test st2
                     WHERE st2.testID = st.testID
                     ORDER BY st.serviceDate DESC
                     LIMIT 1
                    )
WHERE lctn.ClientID = 34
ORDER BY assetID;

我将相关条件转换为 srvc_test 而不是 lctn_test . 这其实没什么区别。我只是觉得为了这个目的引用一个表更简单。

赞(0）回复(0）举报 2021-06-20

nle07wnf2#

如果希望每个联接只有一行，则应在on子句中使用联接表的唯一（或主键）键。那可能是 srvc_test.srvcTestID .

SELECT lctn.testID AS assetID, lctn.ClientID, srvc_test.serviceDate, srvc_test.servicePassed
FROM lctn_test AS lctn
LEFT JOIN srvc_test ON srvc_test.srvcTestID = (
    SELECT srvc_test.srvcTestID
    FROM srvc_test
    WHERE srvc_test.testID = lctn.testID
    ORDER BY srvc_test.serviceDate DESC
    LIMIT 1)
WHERE lctn.ClientID = 34
ORDER BY assetID

赞(0）回复(0）举报 2021-06-20

eqfvzcg83#

你有 a LEFT JOIN b ON a.id = (sub-query) 你应该有 a LEFT JOIN b ON b.id = (sub-query) ```
SELECT lctn.testID AS assetID, lctn.ClientID, srvc_test.serviceDate, srvc_test.servicePassed
FROM lctn_test AS lctn
LEFT JOIN srvc_test ON srvc_test.srvcTestID = (
SELECT srvc_test.srvcTestID
FROM srvc_test
WHERE srvc_test.testID = lctn.testID
ORDER BY srvc_test.serviceDate DESC
LIMIT 1)
WHERE lctn.ClientID = 34
ORDER BY assetID

赞(0）回复(0）举报 2021-06-20

我来回答

限制为1的mysql左联接未返回所需结果

3条答案

相关问题

热门标签

最新问答