3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
请教一对多的表真么统计的sql语句
我有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 对应的人数是多少，并且按照人数多少排序
写了半天没写好，请教老大们，谢谢！
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 对应的人数是多少，并且按照人数多少排序
写了半天没写好，请教老大们，谢谢！
...全文
请发表友善的回复…
发表回复
chinastorm 2008-01-21
非常感谢，好好学习了！结贴！
dawugui 2008-01-21
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 行）
*/
dawugui 2008-01-21
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 行）
*/
chinastorm 2008-01-21
老大，不对呀，rooms数不对
结果应该为
id pnum rooms
----------- ----------- -----------
1 140 185
3 180 196
2 220 115
结果应该为
id pnum rooms
----------- ----------- -----------
1 140 185
3 180 196
2 220 115
dawugui 2008-01-21
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 行）
*/dawugui 2008-01-21
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 行）
*/dobear_0922 2008-01-21
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.人数 descdawugui 2008-01-21
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.人数 chinastorm 2008-01-21
对不起，少说了一点，第二个表还有一列
第二个是项目表
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之和，并且按照人数多少排序
第二个是项目表
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之和，并且按照人数多少排序
chinastorm 2008-01-21
不好意思，看错了
chinastorm 2008-01-21
不对呀，出现错误
服务器: 消息 207，级别 16，状态 3，行 1
列名 'pnum' 无效。
服务器: 消息 207，级别 16，状态 3，行 1
列名 'pnum' 无效。
昵称被占用了 2008-01-21
晕哦，怎么一下发了这么多次
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
kk19840210 2008-01-21
没测试 你看下吧
没测试 你参考下吧
[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 昵称被占用了 2008-01-21
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昵称被占用了 2008-01-21
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昵称被占用了 2008-01-21
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.comnamekk19840210 2008-01-21
没测试 你参考下吧
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