SQL 左连接没有左表中的重复行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22769641/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Left Join without duplicate rows from left table
提问by urooj.org
Please look at the following query:
请看下面的查询：
tbl_Contents
tbl_内容
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
tbl_媒体
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
RESULT
结果
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 are coming twice...!
10012来了两次……！
My query is returning duplicate rows from tbl_Contents (left table in the join)
我的查询从 tbl_Contents 返回重复的行（连接中的左表）
Some rows in tbl_Contents has more than 1 associated rows in tbl_Media. I need all rows from tbl_Contents even if there are Null values exists in the tbl_Media BUT NO DUPLICATE RECORDS.
tbl_Contents 中的某些行在 tbl_Media 中有 1 个以上的关联行。我需要 tbl_Contents 中的所有行，即使 tbl_Media 中存在 Null 值但没有重复记录。
回答by eouw0o83hf
Try an OUTER APPLY
尝试一个 OUTER APPLY
SELECT
C.Content_ID,
C.Content_Title,
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
Alternatively, you could GROUP BYthe results
或者，您可以GROUP BY将结果
SELECT
C.Content_ID,
C.Content_Title,
C.Content_DatePublished,
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_Title,
C.Content_DatePublished,
M.Media_Id
ORDER BY
C.Content_DatePublished ASC
The OUTER APPLYselects a single row (or none) that matches each row from the left table.
在OUTER APPLY选择每一行从左侧表匹配的单个行（或无）。
The GROUP BYperforms the entire join, but then collapses the final result rows on the provided columns.
在GROUP BY执行整个加盟，但随后崩溃所提供的列中的最终结果行。
回答by Gordon Linoff
You can do this using generic SQL with group by:
您可以使用通用 SQL 执行此操作group by：
SELECT C.Content_ID, C.Content_Title, MAX(M.Media_Id)
FROM tbl_Contents C LEFT JOIN
tbl_Media M
ON M.Content_Id = C.Content_Id
GROUP BY C.Content_ID, C.Content_Title
ORDER BY MAX(C.Content_DatePublished) ASC;
Or with a correlated subquery:
或者使用相关子查询：
SELECT C.Content_ID, C.Contt_Title,
(SELECT M.Media_Id
FROM tbl_Media M
WHERE M.Content_Id = C.Content_Id
ORDER BY M.MEDIA_ID DESC
LIMIT 1
) as Media_Id
FROM tbl_Contents C
ORDER BY C.Content_DatePublished ASC;
Of course, the syntax for limit 1varies between databases. Could be top. Or rownum = 1. Or fetch first 1 rows. Or something like that.
当然，limit 1不同数据库的语法有所不同。可能是top。或者rownum = 1。或者fetch first 1 rows。或类似的东西。
回答by Victor Moxley
Using the DISTINCT flag will remove duplicate rows.
使用 DISTINCT 标志将删除重复的行。
SELECT DISTINCT
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
