mysql - 当主查询具有group by子句时，MySql从SubQuery中选择数据

我在MySql数据库中有两个表。

1) networks (NetworkId, NetworkType)
2) users (Id, NetworkId, IpAddress)

使用[NetworkId和IpAddress]定义唯一的用户。

现在，我想在NetworkType上使用group by子句，同时要列出所有用户的数量，如下所示：

SELECT (SELECT Count(distinct IpAddress) FROM users
        WHERE NetworkId in nr.NetworkId ) as UsersCount
FROM networks as nr
GROUP BY NetworkType;

但是由于任何原因，我总是得到零用户。

当我运行以下查询时

SELECT GROUP_CONCATE(nr.NetworkId)
FROM networks as nr
GROUP BY NetworkType;

然后，我得到了以'，'分隔的有效值。

提前致谢。

最佳答案

根据您的新信息更新有关分组依据的信息。

至少为我的努力投票是怎么样的。

Latest SQLFIDDLE

select
    mysub.count,
    nr.`networkid`
from
    `networks` as nr,

(select
    count(`ipaddress`) as count,
    `networkid`
from
    `users`
where
    `networkid`
in (
    select
        `networkid`
    from
        `networks`
   )
group by
    `networkid`) as mysub
    where nr.`networkid` = mysub.`networkid`
    group by nr.`networkid`