数据库是mysql的,共三个表:A表:
id name tel
1 A1 0
2 A2 0
3 A3 0
4 A4 0B表:
id name aid
1 A 1
2 A 1C表:
id name aid
1 A 1现在需要用一条查询语句获取数据:
列出A表中所有字段,并统计出 B表与C表中aid等于A表id的总数。我写的语句:
SELECT a.id,a.name,count(b.aid) AS bnum,count(c.aid) AS cnum, FROM a
Left Join b on a.id = b.aid
Left Join c on a.id = c.aid
group by a.id
可获取的B表使用的总数正确,C表的老出错,似乎老是B表总数的倍数!
特请教!!!
id name tel
1 A1 0
2 A2 0
3 A3 0
4 A4 0B表:
id name aid
1 A 1
2 A 1C表:
id name aid
1 A 1现在需要用一条查询语句获取数据:
列出A表中所有字段,并统计出 B表与C表中aid等于A表id的总数。我写的语句:
SELECT a.id,a.name,count(b.aid) AS bnum,count(c.aid) AS cnum, FROM a
Left Join b on a.id = b.aid
Left Join c on a.id = c.aid
group by a.id
可获取的B表使用的总数正确,C表的老出错,似乎老是B表总数的倍数!
特请教!!!
Left Join
(SELECT AID,COUNT(*) AS MA FROM B GROUP BY AID) B
b on a.id = b.aid
Left Join
((SELECT AID,COUNT(*) AS MC FROM C GROUP BY AID) c on a.id = c.aid ORSELECT a.id,a.name,count(b.aid) AS bnum,count(DISTINCT c.aid) AS cnum, FROM a
Left Join b on a.id = b.aid
Left Join c on a.id = c.aid
group by a.id
Left Join
(SELECT AID,COUNT(*) AS MA FROM B GROUP BY AID) B
b on a.id = b.aid
Left Join
(SELECT AID,COUNT(*) AS MC FROM C GROUP BY AID) c on a.id = c.aid
Left Join
(SELECT AID,COUNT(*) AS MA FROM B GROUP BY AID) b on a.id = b.aid
Left Join
(SELECT AID,COUNT(*) AS MC FROM C GROUP BY AID) c on a.id = c.aid
(select count(*) from b where aid=a.id) as bnum,
(select count(*) from c where aid=a.id) as cnum
from a
Left Join b on a.id = b.aid
Left Join c on a.id = c.aid
group by a.id
=======================================
select a.*,
(select count(*) from b where aid=a.id) as bnum,
(select count(*) from c where aid=a.id) as cnum
from a这两个都可以实现,但不知道那个效率高!
id name aid
1 A 1
2 A 1COUNT(*)与count(DISTINCT c.aid)结果不同,你要取哪个?
如为2,则第2个,如为1 则第1个