Mysql列计数乘法

Question

我有两个正确的mysql查询如下：

select 
count(aboneler.abone_isim),
nas.nasname

from nas
left join aboneler on ( aboneler.nas = nas.id )

group by nas.nasname

第一查询结果和

select 
count(radacct.radacctid)

from nas
left join radacct on ( radacct.nasipaddress = nas.nasname )
where radacct.acctstoptime is null

group by nas.nasname

第二查询结果，但是如果我将这两个查询放在一起，就会得到以下结果：

select 
nas.nasname,
count(aboneler.abone_isim),
count(radacct.radacctid)

from nas
left join aboneler on ( aboneler.nas = nas.id )
left join radacct on ( radacct.nasipaddress = nas.nasname )
where radacct.acctstoptime is null

group by nas.nasname

最后查询结果不应该是这样的。我希望我能解释我的问题。

Answer 1

你可以试试下面的方法-

select A.nasname,cnt1,cnt2 from
(
select nas.nasname,count(aboneler.abone_isim) as cnt1
from nas
left join aboneler on aboneler.nas = nas.id 
group by nas.nasname
)A join 
(
select nas.nasname,count(radacct.radacctid) as cnt2
from nas left join radacct on ( radacct.nasipaddress = nas.nasname )
where radacct.acctstoptime is null
group by nas.nasname
) B on A.nasname=B.nasname