# SQL多表查询统计，请帮帮忙~~~

0

A表

 uid name town village 1 aa 2 4 2 bb 3 5 3 cc 2 4 4 dd 2 6

B表

 cid cname level fatherid 1 A县 1 NULL 2 B镇 2 1 3 C镇 2 1 4 D村 3 2 5 E村 3 2 6 F村 3 3

C表

 uid time pig cow sheep 1 2011-11-1 2 3 4 1 2011-11-2 5 6 7 2 2011-11-3 8 9 10 2 2011-11-4 11 12 13 2 2011-11-5 14 15 16

A表的town对应B表level为2的cid

A表的village对应B表level为3的cid

B表的镇的fatherid为县的cid，村的fatherid为镇的cid

C表的uid对应A表的uid

1.要统计出A县下B镇的动物数量和C镇的动物数量

 cname pig cow sheep B镇 40 45 50 C镇 0 0 0

B镇下有D、E村，D村下有aa、cc，E村下有bb,C镇下F村只有dd，在C表没有数据，所以得到的是以上的数据

2.B镇下下D村E村各自的动物数量的数量

 cname pig cow sheep D村 7 9 11 E村 33 36 39

huiy | 初学一级 | 园豆：59

0
`select A.town,B.cname, sum(C.pig) as pig ,sum(C.cow) as cow ,sum(C.sheep) as sheep ,sum(C.pig)+sum(C.cow)+sum(C.sheep) as total from A,C,B where A.uid=C.uid and A.town = B.Cid and c.fatherid = '县ID'group by A.town,B.cname--找出县下面的所有镇的动物总数select A.uid,A.name, sum(C.pig) as pig ,sum(C.cow) as cow ,sum(C.sheep) as sheep ,sum(C.pig)+sum(C.cow)+sum(C.sheep) as total from A,C where A.uid=C.uid and A.town = '镇ID'group by A.uid,A.name--找出镇下面的所有村的动物总数`

`select A.town,B.cname, sum(C.pig) as pig ,sum(C.cow) as cow ,sum(C.sheep) as sheep from A,C,B where A.uid=C.uid and A.town = B.Cid and c.uid = '县ID'group by A.town,B.cname`

huiy | 园豆：59 (初学一级) | 2011-12-28 22:35

1

select * from c where uid in (select uid from A where town in (select level from b where fatherid = 1))

Sharp_Knife | 园豆：24 (初学一级) | 2011-12-28 16:34

 1 2011-11-1 2 3 4 1 2011-11-2 5 6 7

0

SELECT c.*FROM C
LEFT JOIN (SELECT * FROM A a LEFT JOIN B b ON b.[level]=a.town WHERE (b.fatherid=1 AND b.cname IN('B镇','C镇'))
OR (b.cname='A县')) t on t.uid=c.uid
UNION ALL

SELECT c.*FROM C
LEFT JOIN (SELECT * FROM A a LEFT JOIN B b ON b.[level]=a.town WHERE (b.fatherid=2 AND b.cname IN('D村','E村'))
OR (b.cname='B镇')) t on t.uid=c.uid

sunlary | 园豆：934 (小虾三级) | 2011-12-28 17:32

0

您需要登录以后才能回答，未注册用户请先注册