两个表上的PL / SQL SELECT

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

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

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

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