I've got two tables. One of these contains userData and the other one contains userGroups linked to users which are in a Group. Means like a user can be in 0 or more Groups.
我有两张桌子。其中一个包含userData,另一个包含链接到组中用户的userGroup。像用户这样的手段可以是0个或更多个组。
I'm trying to get this columns with a query used like this:
我正在尝试使用如下所示的查询来获取此列:
SELECT distinct(a.userID), a.userName, Count(b.userID_FID)
FORM userData a,
userGroup b
WHERE a.userID = b.userID_FID
But somehow the Count part returning me a wrong number.
但不知何故,Count部分给我一个错误的数字。
2 个解决方案
#1
1
SELECT max(a.userName), Count(distinct b.userID_FID)
FROM userData a
JOIN userGroup b
ON a.userID = b.userID_FID
GROUP BY a.userID
My suggestion is do not use many tables with from
我的建议是不要使用from的许多表
#2
0
This will get you the number of groups each user is in. To build the test scenario, use this:
create table #groups (userID_FID int ,groups int );
这将获得每个用户所在组的数量。要构建测试场景,请使用:create table #groups(userID_FID int,groups int);
create table #user
(userID int);
insert into #groups values (1, 5);
insert into #groups values (1, 2);
insert into #groups values (1, 3);
insert into #groups values (1, 4);
insert into #groups values (2, 3);
insert into #groups values (2, 4);
insert into #groups values (3, 1);
insert into #groups values (3, 5);
insert into #user values (1);
insert into #user values (2);
insert into #user values (3);
select * from #groups;
select * from #user;
This piece is the query you want, according to the test scenario above:
根据上面的测试场景,这件是你想要的查询:
select userid, count(*) as NumberOfGroups
from #groups g
, #user u
where g.userID_FID = u.userID
group by userID;
#1
1
SELECT max(a.userName), Count(distinct b.userID_FID)
FROM userData a
JOIN userGroup b
ON a.userID = b.userID_FID
GROUP BY a.userID
My suggestion is do not use many tables with from
我的建议是不要使用from的许多表
#2
0
This will get you the number of groups each user is in. To build the test scenario, use this:
create table #groups (userID_FID int ,groups int );
这将获得每个用户所在组的数量。要构建测试场景,请使用:create table #groups(userID_FID int,groups int);
create table #user
(userID int);
insert into #groups values (1, 5);
insert into #groups values (1, 2);
insert into #groups values (1, 3);
insert into #groups values (1, 4);
insert into #groups values (2, 3);
insert into #groups values (2, 4);
insert into #groups values (3, 1);
insert into #groups values (3, 5);
insert into #user values (1);
insert into #user values (2);
insert into #user values (3);
select * from #groups;
select * from #user;
This piece is the query you want, according to the test scenario above:
根据上面的测试场景,这件是你想要的查询:
select userid, count(*) as NumberOfGroups
from #groups g
, #user u
where g.userID_FID = u.userID
group by userID;