主页 / dba / 问题 / 268421
Accepted
Googlebot
Asked: 2020-06-04 05:31:03 +0800 CST

如何在使用子查询时将表与自身连接？

  • 772

我JOIN将自己的表作为

SELECT t1.TagID AS TagID1
    ,t2.TagID AS TagID2
    ,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY t1.TagID, t2.TagID

如何通过以下方式限制为每个表（t1 和 t2）检索的行

WHERE TagID IN(SELECT TagID FROM Tags WHERE Articles>50)

请注意，该表TagMap很大，行数超过 200M，我希望在JOIN.

其实我想限制行数的原因TagMap是表太大，无法执行JOIN。

mysql join

1 个回答

  1. Best Answer

    您使用子选择查询作为 t1 和 t2 的基础

    喜欢

    SELECT 
    t1.TagID AS TagID1, t2.TagID AS TagID2, COUNT(1)
FROM
    (SELECT 
        *
    FROM
        TagMap
    WHERE
        TagID IN (SELECT 
                TagID
            FROM
                Tags
            WHERE
                Articles > 50)) AS t1
        JOIN
    (SELECT 
        *
    FROM
        TagMap
    WHERE
        TagID IN (SELECT 
                TagID
            FROM
                Tags
            WHERE
                Articles > 50)) AS t2 ON t1.ArticleID = t2.ArticleID
        AND t1.TagID <> t2.TagID
GROUP BY t1.TagID , t2.TagID
    • 1

相关问题