日期:2014-05-18 浏览次数:20539 次
--试试这个 select t1.name,t2.b_name,busi_id数量=(select count(busi_id) from c where c_id=t3.c_id) from c t3 left join a t1 on t1.a_id=t3.a_id left join b t2 on t2.b_id=t3.b_id
------解决方案--------------------
1.这个数据库的表设计有问题,a是b 的父,b是c的父,可是a又是c的父,这种结构必须保证数据不能有交叉,如果有交叉,父子关系便出错了.
2.楼主的需求描述不太清楚,你倒底是要输出全部信息,还是输出统计信息?
做了个例子,可以实现,程序应该可以在MYSQL 里运行,但不敢保证适合你的需求.
create table a(a_id int,name varchar(10)) create table b(b_id int,a_id int,b_name varchar(10)) create table c(busi_id int,a_id int,b_id int) insert into a select 1,'aa' insert into a select 2,'bb' insert into b select 1,1,'afds' insert into b select 2,1,'awef' insert into b select 3,2,'vais' insert into c select 51,1,2 insert into c select 13,1,1 --insert into c select 38,2,2 --这样的记录是有问题的 go select a.name,b.b_name,c.busi_id, (select count(*) from c t where t.a_id=a.a_id and t.b_id=b.b_id)ct from a left join b on a.a_id=b.a_id left join c on c.a_id=a.a_id and c.b_id=b.b_id /* name b_name busi_id ct ---------- ---------- ----------- ----------- aa afds 13 1 aa awef 51 1 bb vais NULL 0 (3 行受影响) */ go drop table a,b,c
------解决方案--------------------
--修改 select a.name,b.b_name,c.num from a left join b on a.a_id=b.a_id left join (select busi_id,a_id,b_id,count(1) as num from c group by a_id,b_id,busi_id)c on c.a_id=a.a_id and c.b_id=b.b_id