左连接，从左表中排除重复行

Question

请查看以下查询：

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表（连接中的左表）返回了重复的行。

tbl_Contents的一些行在tbl_Media中有多个关联行。即使在tbl_Media中存在空值，我需要来自tbl_Contents的所有行，但不能有重复记录。

- urooj.org

---

按设计运行，这些行没有被复制，它们各自具有不同的“media_id”。在您的示例中，您会保留哪一行？ - sgeddes

你的 tbl_Media 表中没有 Content_Id。 - Hamlet Hakobyan

我同意这里没有问题。那是两行不同的内容。 - Zane

请检查更新/更正的tbl_Media - 现在有Content_Id列，谢谢。 - urooj.org

Answer 1

尝试使用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

或者，您可以对结果进行 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会选择匹配于左表每一行的单个行（或者没有）。

GROUP BY 执行整个联接操作，但是最终结果会根据指定的列合并为单行。

- eouw0o83hf

---

如果在与SUM或COUNT等聚合函数一起使用时，使用GROUP BY将导致行被重复计算。 - nwhaught

只有当聚合函数不能适当地处理那种情况时... - eouw0o83hf

我认为您忘记在选择M.Media_Id时添加聚合函数了。该列本身不会折叠。以这种形式查询应该会出错。 - vargen_

@vargen_ 这是准确的。我编辑了答案以反映正确的聚合。 - eouw0o83hf

Answer 2

你可以使用通用的 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;

或者使用相关子查询：

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;

当然了，“limit 1”的语法在不同的数据库中有所不同。可能是“top”，或者是“rownum = 1”，或者是“fetch first 1 rows”之类的东西。

- Gordon Linoff

---

根据我从 OP 看到的表 tbl_Media 没有 Content_Id 这一列。 - Hamlet Hakobyan

请检查更新/更正的tbl_Media表——现在已经有Content_Id列了，谢谢。 - urooj.org

关联子查询的性能不会非常糟糕吗？我在大结果集上遇到过这种查询，效果很差。每匹配一行都会执行一个额外的查询。 - mdon88

@mdon88……如果在“tbl_Media(ContentId, Media_id)”上创建索引，性能应该还可以。 - Gordon Linoff

Answer 3

使用 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

- Victor Moxley

---

在查询中添加DISTINCT可能会导致极低的效率（在PostgreSQL中，针对5000个以上的行，我曾看到查询时间增加了10倍）。 - Alexander Kleinhans

Answer 4

表tbl_media中的内容ID 10012出现了两次，因此当tbl_content与tbl_media连接时，它将捕获10012两次，创建一个具有media_id = 1003的行和另一个具有media_id = 1000的行（因此如果您考虑content_id和media_id，则信息实际上并没有重复）。

问题是，在内容10012（1000或1003）中优先考虑哪个媒体？一旦确定了这种关系，您可以调整tbl_media的ID并加入表格，而不会在media_id级别创建重复项。

- Fernando C. A. Pereira

Answer 5

这个查询从10002-10012获取内容ID，且不包含重复项。如果一个内容ID有多个媒体ID，它会获取到第一个找到的媒体ID。要获取最新的媒体ID，在LEFT JOIN的子查询中添加ORDER BY tbl_Media.Media_Id DESC。

SQL Fiddle。

-- Create tbl_Contents table
CREATE TABLE tbl_Contents (
    Content_Id INT,
    Content_Title VARCHAR(255),
    Content_Text TEXT,
    Content_DatePublished DATETIME
);

-- Insert data into tbl_Contents
INSERT INTO tbl_Contents (Content_Id, Content_Title, Content_Text, Content_DatePublished) VALUES
    (10002, 'New case Study',   'New case Study', '2014-03-31 13:39:29.280'),
    (10003, 'New case Study',   'New case Study', '2014-03-31 14:23:06.727'),
    (10004, 'New case Study',   'New case Study', '2014-03-31 14:25:53.143'),
    (10005, 'New case Study',   'New case Study', '2014-03-31 14:26:06.993'),
    (10006, 'New case Study',   'New case Study', '2014-03-31 14:30:18.153'),
    (10007, 'New case Study',   'New case Study', '2014-03-31 14:30:42.513'),
    (10008, 'New case Study',   'New case Study', '2014-03-31 14:31:56.830'),
    (10009, 'New case Study',   'New case Study', '2014-03-31 14:35:18.040'),
    (10010, 'SEO News Title',   'SEO News Text',  '2014-03-31 15:22:15.983'),
    (10011, 'SEO News Title',   'SEO News Text',  '2014-03-31 15:22:30.333'),
    (10012, 'Publish Contents', 'SEO News Text',  '2014-03-31 15:25:11.753');

-- Create tbl_Media table
CREATE TABLE tbl_Media (
    Media_Id INT,
    Media_Title VARCHAR(255),
    Content_Id INT
);

-- Insert data into tbl_Media
INSERT INTO tbl_Media (Media_Id, Media_Title, Content_Id) VALUES
    (1000, 'New case Study', 10012),
    (1001, 'SEO News Title', 10010),
    (1002, 'SEO News Title', 10011),
    (1003, 'Publish Contents', 10012);

-- Query (without duplicates)
SELECT
    tbl_Contents.Content_ID,
    tbl_Contents.Content_Title,
    tbl_Media.Media_Id
FROM tbl_Contents
LEFT JOIN tbl_Media ON tbl_Media.Media_Id = (
    SELECT
        tbl_Media.Media_Id
    FROM tbl_Media
    WHERE tbl_Media.Content_Id = tbl_Contents.Content_Id
    LIMIT 1
)
ORDER BY tbl_Contents.Content_DatePublished ASC;

- Tyler