多表连接+group by select id,记录数=count(b) from (select id=tn1.id,b=tn1.b from tableName as tn1 jion tableName as tn2 on tn1.id = tn2.b) group by id 算法这样,未调试过
create table int_test(id int not null primary key, b tinyint not null default 0) engine myisam; insert into int_test values (1,0),(2,0),(3,1),(4,1),(5,2),(6,2);select a.id,count(b.b) as total from int_test as a left join int_test as b on a.id = b.b group by a.id;query result id total 1 2 2 2 3 0 4 0 5 0 6 0
数据库是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表总数的倍数!特请教!!!
select id,记录数=count(b) from (select id=tn1.id,b=tn1.b from tableName as tn1 jion tableName as tn2 on tn1.id = tn2.b) group by id 算法这样,未调试过
insert into int_test values (1,0),(2,0),(3,1),(4,1),(5,2),(6,2);select a.id,count(b.b) as total from int_test as a left join int_test as b on a.id = b.b group by a.id;query result
id total
1 2
2 2
3 0
4 0
5 0
6 0
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表总数的倍数!特请教!!!