请教一对多的表真么统计的sql语句

[chinastorm]

我有3个表，第一个是单位表
id comname
1 moto
2 nokia
3 ibm
...

第二个是项目表
id itemname parentid
1 银行 1
2 电信 1
3 公路 1
4 银行1 2
5 电信1 2
6 公路1 2
7 公路2 3
8 银行2 3
9 电信4 3
...
第三个表是人数表
id pnum itemid
1 20 2
2 40 3
3 30 1
4 180 6
5 20 2
6 40 5
7 30 1
8 180 7

...

用一条SQL语句统计出
comname 对应的人数是多少，并且按照人数多少排序
写了半天没写好，请教老大们，谢谢！

[kk19840210]

没测试 你参考下吧

select comname,sum(pnum) from taba a left join tabb b on a.id=b.parentid left join tabc c on b.id=c.itemid group by comname group by sum(pnum) desc 

[昵称被占用了]

select a.comname,sum(c.pnum) as pnum
from 单位表 a,项目表 b,人数表 c
where a.id=b.parentid
and b.id=c.itemid
group by a.comname

[昵称被占用了]

select a.comname,sum(c.pnum) as pnum
from 单位表 a,项目表 b,人数表 c
where a.id=b.parentid
and b.id=c.itemid
group by a.comname

[昵称被占用了]

select a.comname,sum(c.pnum) as pnum
from 单位表 a,项目表 b,人数表 c
where a.id=b.parentid
and b.id=c.itemid
group by a.comname

[kk19840210]

没测试 你看下吧

没测试 你参考下吧

[code=SQL]
select comname,sum(pnum) from taba a left join tabb b on a.id=b.parentid left join tabc c on b.id=c.itemid group by comname order by sum(pnum) desc 

[/code]

[昵称被占用了]

晕哦，怎么一下发了这么多次

select a.comname,sum(c.pnum) as pnum
from 单位表 a,项目表 b,人数表 c
where a.id=b.parentid
and b.id=c.itemid
group by a.comname
order by sum(c.pnum) desc

[chinastorm]

不对呀，出现错误
服务器: 消息 207，级别 16，状态 3，行 1
列名 'pnum' 无效。

[chinastorm]

不好意思，看错了

[chinastorm]

对不起，少说了一点，第二个表还有一列
第二个是项目表
id itemname parentid rooms
1 银行 1 60
2 电信 1 80
3 公路 1 45
4 银行1 2 35
5 电信1 2 75
6 公路1 2 86
7 公路2 3 25
8 银行2 3 65
9 电信4 3 25
...
用一条SQL语句统计出
comname 对应的人数是多少，还要加上对应的rooms之和，并且按照人数多少排序

[dawugui]

select a.comname , isnull(m.rooms,0) rooms , isnull(c.人数,0) 人数 from tb1 a
left join
(select parentid , sum(rooms) rooms  from tb2 group by parentid) m on a.id = m.parentid
left join
(select itemid , count(*) 人数 from tb3 group by itemid) n on m.id = n.itemid
order by n.人数 

[dobear_0922]

select a.comname , isnull(m.rooms,0) rooms , isnull(c.人数,0) 人数 from tb1 a
left join
(select parentid , sum(rooms) rooms  from tb2 group by parentid) m on a.id = m.parentid
left join
(select itemid , count(1) 人数 from tb3 group by itemid) n on m.id = n.itemid
order by n.人数 desc

[dawugui]

create table tb1(id int, comname varchar(10) )
insert into tb1 values(1, 'moto') 
insert into tb1 values(2, 'nokia') 
insert into tb1 values(3, 'ibm')
create table tb2 (id int, itemname varchar(10), parentid int, rooms int)
insert into tb2 values(1, '银行' , 1 , 60 )
insert into tb2 values(2, '电信' , 1 , 80 )
insert into tb2 values(3, '公路' , 1 , 45 )
insert into tb2 values(4, '银行1', 2 , 35 )
insert into tb2 values(5, '电信1', 2 , 75 )
insert into tb2 values(6, '公路1', 2 , 86) 
insert into tb2 values(7, '公路2', 3 , 25 )
insert into tb2 values(8, '银行2', 3 , 65 )
insert into tb2 values(9, '电信4', 3 , 25 )
create table tb3 (id int, pnum int, itemid int)
insert into tb3 values(1, 20 , 2 )
insert into tb3 values(2, 40 , 3 )
insert into tb3 values(3, 30 , 1 )
insert into tb3 values(4, 180 , 6 )
insert into tb3 values(5, 20 , 2 )
insert into tb3 values(6, 40 , 5 )
insert into tb3 values(7, 30 , 1 )
insert into tb3 values(8, 180 , 7 )
go

select tb1.id , t.pnum , t.rooms from tb1,
(select m.parentid , sum(pnum) pnum , sum(rooms) rooms from tb2 m , tb3 n where m.id = n.itemid group by m.parentid) t
where tb1.id = t.parentid
order by t.pnum 

drop table tb1,tb2,tb3

/*
id          pnum        rooms       
----------- ----------- ----------- 
1           140         325
3           180         25
2           220         161

（所影响的行数为 3 行）
*/

[dawugui]

create table tb1(id int, comname varchar(10) )
insert into tb1 values(1, 'moto') 
insert into tb1 values(2, 'nokia') 
insert into tb1 values(3, 'ibm')
create table tb2 (id int, itemname varchar(10), parentid int, rooms int)
insert into tb2 values(1, '银行' , 1 , 60 )
insert into tb2 values(2, '电信' , 1 , 80 )
insert into tb2 values(3, '公路' , 1 , 45 )
insert into tb2 values(4, '银行1', 2 , 35 )
insert into tb2 values(5, '电信1', 2 , 75 )
insert into tb2 values(6, '公路1', 2 , 86) 
insert into tb2 values(7, '公路2', 3 , 25 )
insert into tb2 values(8, '银行2', 3 , 65 )
insert into tb2 values(9, '电信4', 3 , 25 )
create table tb3 (id int, pnum int, itemid int)
insert into tb3 values(1, 20 , 2 )
insert into tb3 values(2, 40 , 3 )
insert into tb3 values(3, 30 , 1 )
insert into tb3 values(4, 180 , 6 )
insert into tb3 values(5, 20 , 2 )
insert into tb3 values(6, 40 , 5 )
insert into tb3 values(7, 30 , 1 )
insert into tb3 values(8, 180 , 7 )
go

select tb1.id , sum(tb3.pnum) pnum , sum(tb2.rooms) rooms from tb1,tb2,tb3
where tb1.id = tb2.parentid and tb2.id = tb3.itemid
group by tb1.id
order by pnum

drop table tb1,tb2,tb3
/*
id          pnum        rooms       
----------- ----------- ----------- 
1           140         325
3           180         25
2           220         161

（所影响的行数为 3 行）
*/

[chinastorm]

老大，不对呀，rooms数不对
结果应该为
id pnum rooms
----------- ----------- -----------
1 140 185
3 180 196
2 220 115

[dawugui]

create table tb1(id int, comname varchar(10) )
insert into tb1 values(1, 'moto') 
insert into tb1 values(2, 'nokia') 
insert into tb1 values(3, 'ibm')
create table tb2 (id int, itemname varchar(10), parentid int, rooms int)
insert into tb2 values(1, '银行' , 1 , 60 )
insert into tb2 values(2, '电信' , 1 , 80 )
insert into tb2 values(3, '公路' , 1 , 45 )
insert into tb2 values(4, '银行1', 2 , 35 )
insert into tb2 values(5, '电信1', 2 , 75 )
insert into tb2 values(6, '公路1', 2 , 86) 
insert into tb2 values(7, '公路2', 3 , 25 )
insert into tb2 values(8, '银行2', 3 , 65 )
insert into tb2 values(9, '电信4', 3 , 25 )
create table tb3 (id int, pnum int, itemid int)
insert into tb3 values(1, 20 , 2 )
insert into tb3 values(2, 40 , 3 )
insert into tb3 values(3, 30 , 1 )
insert into tb3 values(4, 180 , 6 )
insert into tb3 values(5, 20 , 2 )
insert into tb3 values(6, 40 , 5 )
insert into tb3 values(7, 30 , 1 )
insert into tb3 values(8, 180 , 7 )
go

select tb1.id , m.pnum , n.rooms from tb1,
(select parentid , sum(rooms) rooms from tb2 group by parentid) n,
(select parentid , sum(pnum) pnum from tb3 , tb2 where tb3.itemid = tb2.id group by parentid) m
where tb1.id = n.parentid and tb1.id = m.parentid
order by pnum 

drop table tb1,tb2,tb3

/*
id          pnum        rooms       
----------- ----------- ----------- 
1           140         185
3           180         115
2           220         196

（所影响的行数为 3 行）
*/

[dawugui]

create table tb1(id int, comname varchar(10) )
insert into tb1 values(1, 'moto') 
insert into tb1 values(2, 'nokia') 
insert into tb1 values(3, 'ibm')
create table tb2 (id int, itemname varchar(10), parentid int, rooms int)
insert into tb2 values(1, '银行' , 1 , 60 )
insert into tb2 values(2, '电信' , 1 , 80 )
insert into tb2 values(3, '公路' , 1 , 45 )
insert into tb2 values(4, '银行1', 2 , 35 )
insert into tb2 values(5, '电信1', 2 , 75 )
insert into tb2 values(6, '公路1', 2 , 86) 
insert into tb2 values(7, '公路2', 3 , 25 )
insert into tb2 values(8, '银行2', 3 , 65 )
insert into tb2 values(9, '电信4', 3 , 25 )
create table tb3 (id int, pnum int, itemid int)
insert into tb3 values(1, 20 , 2 )
insert into tb3 values(2, 40 , 3 )
insert into tb3 values(3, 30 , 1 )
insert into tb3 values(4, 180 , 6 )
insert into tb3 values(5, 20 , 2 )
insert into tb3 values(6, 40 , 5 )
insert into tb3 values(7, 30 , 1 )
insert into tb3 values(8, 180 , 7 )
go

select tb1.id , isnull(m.pnum,0) pnum , isnull(n.rooms,0) rooms from tb1 
left join
(select parentid , sum(rooms) rooms from tb2 group by parentid) n
on tb1.id = n.parentid
left join
(select parentid , sum(pnum) pnum from tb3 , tb2 where tb3.itemid = tb2.id group by parentid) m
on tb1.id = m.parentid
order by pnum 

drop table tb1,tb2,tb3

/*
id          pnum        rooms       
----------- ----------- ----------- 
1           140         185
3           180         115
2           220         196

（所影响的行数为 3 行）
*/

[chinastorm]

非常感谢，好好学习了！结贴！