sql - 如何在 SQL 中使用带有左连接的 Select top 1 子查询

Question

我正在尝试执行此查询，但它不起作用。请帮助我解决此问题我想从带有左连接的子查询中针对相关帖子选择前 1 张图像

SELECT DISTINCT tblAdvertisement.AdvID, 
                tblAdvertisement.CityName, 
                tblAdvertisement.Model, 
                tblAdvertisement.Year, 
                tblAdvertisement.Mileage, 
                tblAdvertisement.imgPath, 
                tblAdvertisement.Price, 
                tblAdvertisement.VoteScore, 
                tblLike.isLike 
FROM            tblAdvertisement 
LEFT JOIN       tblimg 
                ( 
                       SELECT TOP 1 
                              ImgPath 
                       FROM   tblimg ) AS 
ON              tblAdvertisement.AdvID = tblimg.AdvID 
LEFT JOIN       tblLike 
ON              tblAdvertisement.AdvID = tblLike.AdvID 
AND             tblAdvertisement.UserID = @userID 
ORDER BY        AdvID DESC

Answer 1

您需要使用OUTER APPLY:

SELECT DISTINCT A.AdvID, 
                A.CityName, 
                A.Model, 
                A.Year, 
                A.Mileage, 
                I.imgPath, 
                A.Price, 
                A.VoteScore, 
                L.isLike 
FROM            tblAdvertisement A
OUTER APPLY     ( SELECT TOP 1 
                              ImgPath 
                  FROM   tblimg
                  WHERE  AdvID = A.AdvID
                  ORDER BY SomeColumnToOrderBy) I
LEFT JOIN       tblLike L
ON              A.AdvID = L.AdvID 
AND             A.UserID = @userID 
ORDER BY        A.AdvID DESC;

请注意，如果您需要 TOP 1 图片，您应该执行 ORDER BY，否则您会得到一些任意行，而不是您实际需要的行想要。