我有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 对应的人数是多少,并且按照人数多少排序
写了半天没写好,请教老大们,谢谢!
解决方案 »
- |zyciis| SQL查询最出当前商品的商品分类的最高级,并按分类排序号排序,谢谢 有完整测试脚本
- 触发器的 Inserted 有多条数据,我需要调用出每条数据然后执行一下存储过程,用游标处理的话有时候连Insert都Insert不了了。
- sql语句问题,难度好高,在文本中查找值再比较。
- SQL注入攻击问题
- 求救,复制表结构问题!!!
- 数据库
- BULK INSERT 将文本文件数据插入数据库中时出现“超时已过期”如何处理(二) (邹建大哥帮忙看一下)
- 能不能用Excel做前端,连接和访问后台的SQLServer数据库??
- 关于因为该列没有包含在聚合函数或 GROUP BY 子句中。
- 关于全文检索的离奇问题,错误编号在帮助文档google都找不到
- 请教一个sql语句的优化,我在子查询中用到count(distinct)的时候,效率低的不行
- 删除论坛某版块时禁止其他用户提交该版块问题,该如何做?
没测试 你参考下吧
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
from 单位表 a,项目表 b,人数表 c
where a.id=b.parentid
and b.id=c.itemid
group by a.comname
from 单位表 a,项目表 b,人数表 c
where a.id=b.parentid
and b.id=c.itemid
group by a.comname
from 单位表 a,项目表 b,人数表 c
where a.id=b.parentid
and b.id=c.itemid
group by a.comname
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]
from 单位表 a,项目表 b,人数表 c
where a.id=b.parentid
and b.id=c.itemid
group by a.comname
order by sum(c.pnum) desc
服务器: 消息 207,级别 16,状态 3,行 1
列名 'pnum' 无效。
第二个是项目表
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之和,并且按照人数多少排序
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.人数
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
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 )
goselect 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 行)
*/
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 )
goselect 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 pnumdrop table tb1,tb2,tb3
/*
id pnum rooms
----------- ----------- -----------
1 140 325
3 180 25
2 220 161(所影响的行数为 3 行)
*/
结果应该为
id pnum rooms
----------- ----------- -----------
1 140 185
3 180 196
2 220 115
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 )
goselect 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 行)
*/
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 )
goselect 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 行)
*/