将记录限制在自连接查询中连接的一侧

发布于 2024-12-06 20:58:48 字数 732 浏览 0 评论 0原文

考虑一个表（名称 Term），如下所示：

[Id] [int] IDENTITY(1,1) NOT NULL,
[CategoryId] [int] NOT NULL,
[Label] [nvarchar](256) NOT NULL,
[Uri] [nvarchar](1024) NOT NULL

现在，考虑定义了两个类别：CategoryId 1 和 CategoryId 2，并且有多个术语属于类别 1 或类别 2。我试图在这两个类别中或在这两个类别的单个类别中查找具有相同 URI 但不同标签的术语。为此，我尝试编写一个自连接 SQL：

SELECT
    t1.Id AS TermId1,
    t2.Id AS TermId2
FROM
    Term t1 INNER JOIN Term t2 ON t1.Uri = t2.Uri
WHERE
    t1.CategoryId IN (@CategoryId1, @CategoryId2) AND
    t2.CategoryId IN (@CategoryId1, @CategoryId2) AND
    t1.Label <> t2.Label

这工作正常，但每行返回两次...例如，

TermId1、TermId2 TermId2, TermId1

如果termId1 与termId2 不同，则不需要倒序结果。那么，我该如何一次性得到结果呢？

感谢您的帮助，

原文

Consider a table (name Term) as follows:

[Id] [int] IDENTITY(1,1) NOT NULL,
[CategoryId] [int] NOT NULL,
[Label] [nvarchar](256) NOT NULL,
[Uri] [nvarchar](1024) NOT NULL

Now, consider that there are two categories defined: CategoryId 1 and CategoryId 2 and there are multiple terms that belong either to category1 or category2. I'm trying to find those Terms that have the same URI but different labels in these two categories, or within a single category of those two. To do so, I tried to write a self join SQL:

SELECT
    t1.Id AS TermId1,
    t2.Id AS TermId2
FROM
    Term t1 INNER JOIN Term t2 ON t1.Uri = t2.Uri
WHERE
    t1.CategoryId IN (@CategoryId1, @CategoryId2) AND
    t2.CategoryId IN (@CategoryId1, @CategoryId2) AND
    t1.Label <> t2.Label

This works fine, but each row is returned twice... For example,

TermId1, TermId2
TermId2, TermId1

If the termId1 is different from termId2, then there's no need to have the reverse order result. So, how am I supposed to get results once?

Thank you for your help,

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无声情话 2024-12-13 20:58:48

尝试一下。您可能不需要分组依据

    SELECT t1.Id AS TermId1, t2.Id AS TermId2
    FROM
        Term t1 
        INNER JOIN Term t2 
          ON t1.Uri = t2.Uri
         AND t1.Label <> t2.Label
         AND t1.Id < t2.Id      
    WHERE
            t1.CategoryId IN (@CategoryId1, @CategoryId2) 
        and t2.CategoryId IN (@CategoryId1, @CategoryId2)          
    group by t1.Id, t2.Id
    order by t1.Id, t2.Id

Give this a try. You may not need the group by

    SELECT t1.Id AS TermId1, t2.Id AS TermId2
    FROM
        Term t1 
        INNER JOIN Term t2 
          ON t1.Uri = t2.Uri
         AND t1.Label <> t2.Label
         AND t1.Id < t2.Id      
    WHERE
            t1.CategoryId IN (@CategoryId1, @CategoryId2) 
        and t2.CategoryId IN (@CategoryId1, @CategoryId2)          
    group by t1.Id, t2.Id
    order by t1.Id, t2.Id

回复收藏 0 原文

~没有更多了~