A表
部门编号(cid) 所属状态(state)
57 0
57 0
57 2
29 1
58 1
59 3
……..
B表
部门名称(name) 父节点(fid) 区域(area) 部门编号(cid)
办公室 -100 1 3
办公室A 44 1 57
办公室B 44 1 58
人事部 -100 6 8
人事部A 36 6 29
人事部B 36 6 59
查询统计结果为
部门名称(name) 状态0数量 状态1数量 状态2数量 状态3数量
办公室 0 0 0 0
办公室A 0 0 1 0
办公室B 0 1 0 0
人事部 0 1 0 1
说明:部门编号连接两张表,根据区域统计出各种状态的数量(就是部门编号为29,59的状态数据都属于区域6),然后把区域中父节点为-100的作为查询出数据的部门名称
也就是统计同一区域内(例外:办公室都属于区域1,按照部门单独统计) 部门 所属状态的个数
数据库为ORCLE 10g ….
部门编号(cid) 所属状态(state)
57 0
57 0
57 2
29 1
58 1
59 3
……..
B表
部门名称(name) 父节点(fid) 区域(area) 部门编号(cid)
办公室 -100 1 3
办公室A 44 1 57
办公室B 44 1 58
人事部 -100 6 8
人事部A 36 6 29
人事部B 36 6 59
查询统计结果为
部门名称(name) 状态0数量 状态1数量 状态2数量 状态3数量
办公室 0 0 0 0
办公室A 0 0 1 0
办公室B 0 1 0 0
人事部 0 1 0 1
说明:部门编号连接两张表,根据区域统计出各种状态的数量(就是部门编号为29,59的状态数据都属于区域6),然后把区域中父节点为-100的作为查询出数据的部门名称
也就是统计同一区域内(例外:办公室都属于区域1,按照部门单独统计) 部门 所属状态的个数
数据库为ORCLE 10g ….
解决方案 »
- 请问高手,11g hash分区后的默认排序,是怎么排的?
- 局域网里怎样连接远程的oracle数据库
- Oracle 事务概念理解问题
- 搜索结果页面中,在点击下一页时列出了所有的新闻,这是什么原因?
- 大家推荐一本oracle的教材!
- 把 test.csv 导入oradb数据库时,为什么凡是有行记录是null的都导入不进去?
- USER.TABLE 发生了变化,触发器/函数不能读
- 怎样在Java中调用Oracle中的存储过程?
- 在linux中怎么启动OEM,已经添加了资料档案库,用什么命令。
- 求教下面的公司实施oracle ebs的能力怎么样
- 在64位的操作系统(Windows Server 2003)上能否安装32位的Oracle数据库?
- oracle 性能比较,谁快谁慢?
select count(decode(a.state,0,1))状态0数量,
count(decode(a.state,1,1))状态1数量,
count(decode(a.state,2,1))状态2数量,
count(decode(a.state,3,1))状态3数量
from a,b
where a.cid=b.cid
and b.fid=-100
group by b.name
是这个意思吗?
上面的说明我看不明白
“把区域中父节点为-100的作为查询出数据的部门名称”
按这个描述的话应该是:select max(decode(b.fid,-100,b.name)),
count(decode(a.state,0,1))状态0数量,
count(decode(a.state,1,1))状态1数量,
count(decode(a.state,2,1))状态2数量,
count(decode(a.state,3,1))状态3数量
from a right join b
on a.cid=b.cid
group by b.area
和你写的结果不符
else max(decode(b.fid,-100,b.name))end name,
count(decode(a.state,0,1))状态0数量,
count(decode(a.state,1,1))状态1数量,
count(decode(a.state,2,1))状态2数量,
count(decode(a.state,3,1))状态3数量
from a right join b
on a.cid=b.cid
group by decode(b.area,1,b.area+rownum*0.1,b.area)
试试
count(decode(a.state,0,1)) "状态0数量",
count(decode(a.state,1,1)) "状态1数量",
count(decode(a.state,2,1)) "状态2数量",
count(decode(a.state,3,1)) "状态3数量"
from a, b
where a.cid(+) = b.cid
group by decode(area,6,substr(name,1,3),name);
第一行
decode(area,6,substr(name,1,3),name)
else max(decode(b.fid,-100,b.name))end name,
count(decode(a.state,0,1))状态0数量,
count(decode(a.state,1,1))状态1数量,
count(decode(a.state,2,1))状态2数量,
count(decode(a.state,3,1))状态3数量
from aa a right join bb b
on a.cid=b.cid
group by decode(b.area,1,b.name,b.area)
order by max(b.area)
我不知道你的数据类型是什么,不符的自己转一下
排序可以自己变更,楼主测试下
这个代码不需要部门名称长度有规律
select case max(b.area) when 1 then max(b.name)
else max(decode(b.fid,-100,b.name))end name,
count(decode(a.state,0,1))状态0数量,
count(decode(a.state,1,1))状态1数量,
count(decode(a.state,2,1))状态2数量,
count(decode(a.state,3,1))状态3数量
from aa a right join bb b
on a.cid=b.cid
group by decode(b.area,1,b.name,b.area)
order by name
wildwave,谢谢你。估计好了。我调试一下,完了结贴
count(decode(a.state,0,1)) "状态0数量",
count(decode(a.state,1,1)) "状态1数量",
count(decode(a.state,2,1)) "状态2数量",
count(decode(a.state,3,1)) "状态3数量"
from a,
(select b1.*, case when b1.area = 1 then b1.name when b1.fid=-100 then b1. name else select b2.name from b b2 where b2.area=b1.area and b2.area=-100 where rownum < 1 end pcname from b b1) c where c.cid=a.cid(+)
group by c.pcname
select b.month,case max(b.area) when 1 then max(b.name)
else max(decode(b.fid,-100,b.name))end name,
count(decode(a.state,0,1))状态0数量,
count(decode(a.state,1,1))状态1数量,
count(decode(a.state,2,1))状态2数量,
count(decode(a.state,3,1))状态3数量
from aa a right join (
select bb.*,cc.month from bb,(
select add_months(to_date(:startmonth,'yyyymm'),rownum-1)month
from dual
connect by rownum<=months_between(to_date(:endmonth,'yyyymm'),
to_date(:startmonth,'yyyymm'))+1) b
on a.cid=b.cid
and to_date(a.month,'yyyymm')=b.month
group by b.month,decode(b.area,1,b.name,b.area)
order by b.month,name
你的表看起来是这样的,如果a表中每个月都包含所有部门编号的话,就简单多了
select a.month,case max(b.area) when 1 then max(b.name)
else max(decode(b.fid,-100,b.name))end name,
count(decode(a.state,0,1))状态0数量,
count(decode(a.state,1,1))状态1数量,
count(decode(a.state,2,1))状态2数量,
count(decode(a.state,3,1))状态3数量
from aa a inner join bb b
on a.cid=b.cid
where a.month>=:startmonth and a.month<=:endmonth
group by a.month,decode(b.area,1,b.name,b.area)
order by a.month,name
select case max(b.area) when 1 then max(b.name)
else max(decode(b.fid,-100,b.name))end name,
count(decode(a.state,0,1))状态0数量,
count(decode(a.state,1,1))状态1数量,
count(decode(a.state,2,1))状态2数量,
count(decode(a.state,3,1))状态3数量
from aa a right join bb b
on a.cid=b.cid
group by decode(b.area,1,b.name,b.area)
order by name
就想在在from aa 后面添加一句 where a.month like '%08%'.
我在on a.cid=b.cid后面添加了where a.month like '%08%'后,区域不为1的name值为空。
试试这个
select case max(b.area) when 1 then max(b.name)
else max(c.name) end name,
count(decode(a.state,0,1))状态0数量,
count(decode(a.state,1,1))状态1数量,
count(decode(a.state,2,1))状态2数量,
count(decode(a.state,3,1))状态3数量
from aa a inner join bb b
inner join (select name,area from bb where fid=-100)c
on a.cid=b.cid and b.area=c.area
group by decode(b.area,1,b.name,b.area)
order by name
添加and a.month like '%08%'
结果正确了吗
select case max(b.area) when 1 then max(b.name)
else max(c.name) end name,
count(decode(a.state,0,1))状态0数量,
count(decode(a.state,1,1))状态1数量,
count(decode(a.state,2,1))状态2数量,
count(decode(a.state,3,1))状态3数量
from aa a, bb b
, (select name,area from bb where fid=-100)c
where b.area=c.area
and a.cid=b.cid
and a.month like '%08%'
group by decode(b.area,1,b.name,b.area)
order by name
我用你的例子做测试,正常(没加month字段)