请看下面的查询：
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执行整个连接,但是在提供的列上折叠最终的结果行.
