mysql - 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

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

标签： mysqlsql

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