仅当找到多次时才选择 sql

发布于 2025-01-17 19:18:24 字数 703 浏览 0 评论 0原文

我有一个包含用户 ID 和功能的表。我只想选择那些选择了 1 个以上功能的用户。查看表格

    declare @tbl table (userid int, feature nvarchar(10))
    insert into @tbl(userid, feature)
    select 1, 'abc'
    insert into @tbl(userid, feature)
    select 1, 'xyz'
    insert into @tbl(userid, feature)
    select 2, 'abc'
    insert into @tbl(userid, feature)
    select 3, 'abc'
    insert into @tbl(userid, feature)
    select 3, 'xyz'
    insert into @tbl(userid, feature)
    select 3, 'pqr'

在此处输入图像描述

我的查询应该只返回计数 = 2(用户 ID 1 和 3,因为它们都具有多个关联的功能)。换句话说,我想要具有与其关联的多个功能的用户 ID 的计数。

I have a table with userid and feature. I want to select only those users with more than 1 feature selected. check out the table

    declare @tbl table (userid int, feature nvarchar(10))
    insert into @tbl(userid, feature)
    select 1, 'abc'
    insert into @tbl(userid, feature)
    select 1, 'xyz'
    insert into @tbl(userid, feature)
    select 2, 'abc'
    insert into @tbl(userid, feature)
    select 3, 'abc'
    insert into @tbl(userid, feature)
    select 3, 'xyz'
    insert into @tbl(userid, feature)
    select 3, 'pqr'

enter image description here

My query should only return the count = 2 (userids 1 and 3 as they both have multiple features associated). In other words I want the count of userids with multiple features associated with them.

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

扫码二维码加入Web技术交流群

发布评论

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

评论(2

伴随着你 2025-01-24 19:18:24

您可以尝试在HAVING中使用带有条件聚合函数的JOIN

SELECT t1.userid
FROM @tbl t1
INNER JOIN @tbl t2 
ON t1.feature = t2.feature
AND t1.userid <> t2.userid
GROUP BY t1.userid
HAVING COUNT(DISTINCT t1.feature) > 1

You can try to use JOIN with condition aggregate function in HAVING

SELECT t1.userid
FROM @tbl t1
INNER JOIN @tbl t2 
ON t1.feature = t2.feature
AND t1.userid <> t2.userid
GROUP BY t1.userid
HAVING COUNT(DISTINCT t1.feature) > 1
转角预定愛 2025-01-24 19:18:24

这是一个简单的聚合:用户和计数组组。

select userid
from @tbl
group by userid
having count(*) > 1;

That's a simple aggregation: group by user and count.

select userid
from @tbl
group by userid
having count(*) > 1;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文