两表关联查询

Question

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 ….

Answer 1

办公室AB父节点都不是-100，为什么也出现在结果中？
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

Answer 2

忘了加b.name字段。楼主补上
是这个意思吗？

Answer 3

楼主能不能重新描述下查询规则
上面的说明我看不明白
“把区域中父节点为-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
和你写的结果不符

Answer 4

select case 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 a right join b 
on a.cid=b.cid 
group by decode(b.area,1,b.area+rownum*0.1,b.area)
试试

Answer 5

报，数据类型不一致，应为char，实际获得为number，我将1改为to_char(1)后，提示为不是group by 表达式

Answer 6

select  decode(area,6,substr(name,1,2),name) 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, b 
where   a.cid(+) = b.cid
group by decode(area,6,substr(name,1,3),name);

Answer 7

decode(area,6,substr(name,1,2),name)
第一行
decode(area,6,substr(name,1,3),name)

Answer 8

第一行subtr(name,1,3)，不是1,2不过还是不满足你的要求，没有用到-100

Answer 9

如果人事部的名字规律就如你给的例子那样可以用我那个sql

Answer 10

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 max(b.area)
我不知道你的数据类型是什么，不符的自己转一下

Answer 11

。。没错
排序可以自己变更，楼主测试下
这个代码不需要部门名称长度有规律
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

Answer 12

 
wildwave，谢谢你。估计好了。我调试一下，完了结贴

Answer 13

如果每个区域都有一个fid值为-100的记录的话，应该不会出现名字为空的情况

Answer 14

试试这个select   c.pcname "部门名称",
        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

Answer 15

测试了一下，基本好着，就是多出来了一条空白数据（无所谓），wildwave，在求你一点，如果a表中有个月份字段，我根据传入的月份查询每个月的统计数据，在你的语句中该如何？？？再次拜谢

Answer 16

group by前加where a.month_column = 'month'不可以么

Answer 17

这个用了后区域不为1的name为空

Answer 18

根据传入的月份查询每个月还是单月？如果传入的是一个月份范围的话（假设其为yyyymm的字符串格式）
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

Answer 19

这个语句基于a表中并非每个月都有包含所有部门编号的记录的情况
你的表看起来是这样的，如果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

Answer 20

谢谢你，我的意思是先按照月份把数据查出来，在统计
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值为空。

Answer 21

因为你查出来的结果里没有父节点值为-100的记录吧
试试这个
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 

Answer 22

在group处提示缺失关键字，其实我现在添加一个 like month = ‘%08%’，就可以

Answer 23

啊，不会吧
添加and a.month like '%08%'
结果正确了吗

Answer 24

晕，粗心了
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 

Answer 25

...你是不是用了nvl()函数和外连接，将空置转换成0了

Answer 26

那么你检查下原表的数据
我用你的例子做测试，正常（没加month字段）