过滤后，从SQL Server获取相关的标签 [英] Getting related tags from SQL Server when you have filtered down

问题描述

我以前问过这个问题，得到了很好的工作答案。

要获得的相关标签的查询是什么像堆栈溢出

但是，我意识到，SOF实际上更进一步，因为它支持多个标签深入

我的意思是，如果点击标签 C＃，这将过滤到20,000个问题，它会显示所有相关标签的问题，也有 C＃作为标签。

但是，我可以点击标签收藏，现在看到标签总数 C＃和收藏。也许500个问题

此外，它仍然会显示相关的标签，这些标签中存在的问题也有标签 C＃ 和收藏

，您可以继续筛选。

so在上面的链接中的答案，只支持1个单一的标签。

在sql中如何让这个工作能够完全深入了解并显示相关标签的数量并允许用户继续选择其他标签进行深入挖掘。

解决方案

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

  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 
）
  

或者如果要包含链接：

  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 
标签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 
  

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

what is the query to get "related tags" like in stack overflow

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

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.

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

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

and you can keep filtering down.

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

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.

解决方案

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

The same for three tags:

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

这篇关于过滤后，从SQL Server获取相关的标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！