左联接没有左表中的重复行 [英] Left Join without duplicate rows from left table
问题描述
请看下面的查询:
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
查询
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(连接中的左表)返回重复行
My query is returning duplicate rows from tbl_Contents (left table in the join)
tbl_Contents 中的某些行在 tbl_Media 中有超过 1 个关联的行.我需要 tbl_Contents 中的所有行,即使 tbl_Media 中存在 Null 值但没有重复记录.
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.
推荐答案
试试 外部应用
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
或者,您可以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
OUTER APPLY
选择与左表中的每一行匹配的单行(或无).
The OUTER APPLY
selects a single row (or none) that matches each row from the left table.
GROUP BY
执行整个连接,但随后折叠提供的列上的最终结果行.
The GROUP BY
performs the entire join, but then collapses the final result rows on the provided columns.
这篇关于左联接没有左表中的重复行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!