过滤后从 SQL Server 获取相关标签

2022-01-18 00:00:00 tags sql-server database-design

我之前问过这个问题并得到了很好的答案.

I asked this question before and got a great working answer.

查询是什么获取相关标签"就像堆栈溢出一样

但我意识到 SOF 实际上更进一步，因为它支持多标签向下钻取

but i realized that SOF actually takes it one step further as it supports multiple tag drilldown

我的意思是，如果点击标签 C#，它会过滤到 20,000 个问题，它会显示所有与 C# 相关的问题的标签一个标签.

what i mean is, if click on the tag C#, that will filter to 20,000 questions and it will show me all related tags on questions that also have C# as a tag.

然后，我可以点击Collections"标签，现在可以看到带有C#"标签和Collections"标签的问题总数强>".也许那 500 个问题

but then, i can click on the tag "Collections" and now see total number of questions with the tag "C#" and "Collections". maybe that 500 questions

此外，它还会显示相关标签，这些标签存在于问题中，也有标签C#"和Collections"

in addition, it will still show me related tags, which are tags that exist in questions which also have tag "C#" and "Collections"

你可以继续过滤.

所以上面链接中的答案，只支持1个单标签.

so the answer in the above link, only support 1 single tag.

在 sql 中，您将如何使其能够完全向下钻取并显示相关标签的数量，并允许用户继续选择其他标签进行向下钻取.

in sql how would you get this to work to be able to completely drill down and show count of related tags and allow the user to keep choosing additional tags for drilldown.

推荐答案

我会使用这样的东西作为第一个版本.@FirstTagID 和 @SecondTagID 必须按 ID 排序.您可以通过向内部查询添加更多连接来查询更多标签.

I'd use something like this as the first version. @FirstTagID and @SecondTagID must be sorted by ID. You can query on more tags by adding more joins to the inner query.

SELECT tagName
FROM tags
WHERE id IN (
    SELECT tagID
    FROM tagsBridge
    WHERE linkID IN (
        SELECT t1.linkID
        FROM
            tagsBridge t1
            JOIN tagsBridge t2 ON t2.linkID=t1.linkID AND t2.tagID > t1.tagID
        WHERE t1.tagID=@FirstTagID AND t2.tagID=@SecondTagID
    )
    AND tagID!=@FirstTagID AND tagID!=@SecondTagID
)

或者如果你想包含链接的数量:

Or if you want to include the number of links:

SELECT t.tagName, count(*)
FROM 
    tags t
    JOIN tagsBridge tb ON tb.tagID = t.id
WHERE
    tb.linkID IN (
        SELECT t1.linkID
        FROM
            tagsBridge t1
            JOIN tagsBridge t2 ON t2.linkID=t1.linkID AND t2.tagID > t1.tagID
        WHERE t1.tagID=@FirstTagID AND t2.tagID=@SecondTagID
    )
    AND t.tagID!=@FirstTagID AND t.tagID!=@SecondTagID
GROUP BY t.tagName

三个标签相同:

SELECT t.tagName, count(*)
FROM 
    tags t
    JOIN tagsBridge tb ON tb.tagID = t.id
WHERE
    tb.linkID IN (
        SELECT t1.linkID
        FROM
            tagsBridge t1
            JOIN tagsBridge t2 ON t2.linkID=t1.linkID AND t2.tagID > t1.tagID
            JOIN tagsBridge t3 ON t3.linkID=t2.linkID AND t3.tagID > t2.tagID
        WHERE t1.tagID=@FirstTagID AND t2.tagID=@SecondTagID AND t3.tagID=@ThirdTagID
    )
    AND t.tagID!=@FirstTagID AND t.tagID!=@SecondTagID AND t.tagID!=@ThirdTagID
GROUP BY t.tagName

相关文章