sql – 左连接左侧表中没有重复的行

发布时间：2020-12-12 16:09:06 所属栏目：MsSql教程来源：网络整理

导读：请看下面的查询： tbl_Contents Content_Id Content_Title Content_Text10002 New case Study New case Study10003 New case Study New case Study10004 New case Study New case Study10005 New case Study New case Study10006 New case Study New case Stu

请看下面的查询：

tbl_Contents

Content_Id  Content_Title    Content_Text
10002   New case Study   New case Study
10003   New case Study   New case Study
10004   New case Study   New case Study
10005   New case Study   New case Study
10006   New case Study   New case Study
10007   New case Study   New case Study
10008   New case Study   New case Study
10009   New case Study   New case Study
10010   SEO News Title   SEO News Text
10011   SEO News Title   SEO News Text
10012   Publish Contents SEO News Text

tbl_Media

Media_Id    Media_Title  Content_Id
1000    New case Study   10012
1001    SEO News Title   10010
1002    SEO News Title   10011
1003    Publish Contents 10012

QUERY

SELECT 
C.Content_ID,C.Content_Title,M.Media_Id

FROM tbl_Contents C
LEFT JOIN tbl_Media M ON M.Content_Id = C.Content_Id 
ORDER BY C.Content_DatePublished ASC

结果

10002   New case Study  2014-03-31 13:39:29.280 NULL
10003   New case Study  2014-03-31 14:23:06.727 NULL
10004   New case Study  2014-03-31 14:25:53.143 NULL
10005   New case Study  2014-03-31 14:26:06.993 NULL
10006   New case Study  2014-03-31 14:30:18.153 NULL
10007   New case Study  2014-03-31 14:30:42.513 NULL
10008   New case Study  2014-03-31 14:31:56.830 NULL
10009   New case Study  2014-03-31 14:35:18.040 NULL
10010   SEO News Title  2014-03-31 15:22:15.983 1001
10011   SEO News Title  2014-03-31 15:22:30.333 1002
10012   Publish         2014-03-31 15:25:11.753 1000
10012   Publish         2014-03-31 15:25:11.753 1003

10012来了两次！

我的查询是从tbl_Contents返回重复的行(连接中的左表)

tbl_Contents中的某些行在tbl_Media中有多个关联的行.
即使tbl_Media中没有存在Null值,也不需要重复记录,我需要tbl_Contents中的所有行.

解决方法

尝试一个OUTER APPLY

SELECT 
    C.Content_ID,C.Content_DatePublished,M.Media_Id
FROM 
    tbl_Contents C
    OUTER APPLY
    (
        SELECT TOP 1 *
        FROM tbl_Media M 
        WHERE M.Content_Id = C.Content_Id 
    ) m
ORDER BY 
    C.Content_DatePublished ASC

或者,您可以GROUP BY结果

SELECT 
    C.Content_ID,M.Media_Id
FROM 
    tbl_Contents C
    LEFT OUTER JOIN tbl_Media M ON M.Content_Id = C.Content_Id 
GROUP BY
    C.Content_ID,C.Content_DatePublished
ORDER BY 
    C.Content_DatePublished ASC

外部应用程序选择与左侧表格中的每一行匹配的单个行(或无).

GROUP BY执行整个连接,但是在提供的列上折叠最终的结果行.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!