SQL选择不存在的优先级

发布于 2024-10-12 11:03:58 字数 545 浏览 1 评论 0原文

我对如何构造我需要的查询感到非常困惑：

select distinct NodeID from GroupsTable
                    where GroupID in
                    (
                        select GroupID from UserGroupsTable
                        where UserID = @UserID
                    )

我需要从 GroupsTable 中选择 NodeID，其中用户属于多个组，除非用户所属的任何组都没有那个 NodeID。

上面的代码仅从 GroupsTable 中选择出现在任一用户组中的 NodeID。

所以：

组|节点
1|A
1|B
2|一个

例如，我只需要选择 B。

有什么想法吗？

原文

I'm quite confused as to how I'd structure a query I need:

select distinct NodeID from GroupsTable
                    where GroupID in
                    (
                        select GroupID from UserGroupsTable
                        where UserID = @UserID
                    )

I need to select NodeID from GroupsTable where the User belongs to multiple groups, UNLESS any group that the user belongs to does not have that NodeID.

The above code only selects a NodeID from GroupsTable where it apears in any one of the users groups.

So:

Group|Node
1|A
1|B
2|A

I need only B to be selected for example.

Any Ideas?

分享到QQ

分享到微博

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

发布评论

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

心如狂蝶 2024-10-19 11:03:58

declare @UserID int
select G.NodeID
from GroupsTable G
inner join
(
    select GroupID, count(*) over () GroupCount
    from UserGroupsTable
    where UserID = @UserID
) UG on UG.GroupID = G.GroupID
GROUP BY G.NodeID, UG.GroupCount
HAVING COUNT(*) != UG.GroupCount OR UG.GroupCount = 1
-- count=1 is special. it will always equal groupcount, but let it through

（假设这是 SQL Server 且版本 >= 2005 用于使用 count(*) ）

@answer HAVING 条件已更改以下注释

declare @UserID int
select G.NodeID
from GroupsTable G
inner join
(
    select GroupID, count(*) over () GroupCount
    from UserGroupsTable
    where UserID = @UserID
) UG on UG.GroupID = G.GroupID
GROUP BY G.NodeID, UG.GroupCount
HAVING COUNT(*) != UG.GroupCount OR UG.GroupCount = 1
-- count=1 is special. it will always equal groupcount, but let it through

(assuming this is SQL Server and version >= 2005 for using the count(*) over)

@answer HAVING condition changed following comments

回复收藏 0 原文

何其悲哀 2024-10-19 11:03:58

您可以使用 having 子句来确保每个节点的组计数小于用户所在的所有组的计数：

select  g.NodeID
from    GroupsTable g
join    UserGroupsTable ug 
on      g.GroupID = ug.GroupID
where   ug.UserID = 1
group by
        ug.UserID
,       g.NodeID
having  COUNT(distinct g.GroupID) < 
        (
        select  COUNT(distinct GroupID) 
        from    UserGroupsTable ug2 
        where   ug2.UserID = ug.UserID
        )

You can use a having clause to ensure that the count of groups per node is less than the count of all groups the user is in:

select  g.NodeID
from    GroupsTable g
join    UserGroupsTable ug 
on      g.GroupID = ug.GroupID
where   ug.UserID = 1
group by
        ug.UserID
,       g.NodeID
having  COUNT(distinct g.GroupID) < 
        (
        select  COUNT(distinct GroupID) 
        from    UserGroupsTable ug2 
        where   ug2.UserID = ug.UserID
        )

回复收藏 0 原文

~没有更多了~