当主查询有groupby子句时，MySql从SubQuery中选择数据

Question

我在MySql db中有两个表。

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

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

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

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;

然后我将得到与'，‘分隔的有效值。

提前谢谢。

Answer 1

根据您的新信息更新有关您需要的组的内容。

至少为我的努力投一票吧。

最新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`