百分悬赏标值函数或存储过程

[grn0515]

1、已知条件：
A表,表结构:ID(编号) PID(父级编号) 然后有如下数据：
ID PID
1 0
2 1
3 1
4 2
5 2
6 3
7 4
8 5
9 6
10 6
B表，表结构：ID (编号) 状态(0关机;1开机;2:待机) , 然后有如下数据：
ID State
7 0
7 1
7 0
8 1
8 0
8 1
8 0
9 1
9 2
9 1
9 0
9 1
9 0
10 0
A表和B表的ID是相等的

2、求: 当传入ID等于1的时候，我要算出它和它子集的4条状态统计数据,
a.开机到关机是多少次
b.开机到待机是多少次
c.待机到关机是多少次
d待机到开机是多少次
关键问题是B表里面只会有最底层的数据，然后传入要查询的参数ID不会是最底层的ID,
麻烦的问题是上一条数据不好查，哪位大神给个标值函数或存储过程

3、想要结果，当传入要查询的参数ID=1时，
ID 　a b c d
1 5 1 0 1
2 3 0 0 0
3 2 1 0 1

[CT_LXL]

引用 楼主 grn0515 的回复:

1、已知条件： A表,表结构:ID(编号) PID(父级编号) 然后有如下数据： ID PID 1 0 2 1 3 1 4 2 5 2 6 3 7 4 8 5 9 6 10 6 B表，表结构：ID (编号) 状态(0关机;1开机;2:待机) , 然后有如下数据： ID State 7 0 7 1 7 0 8 1 8 0 8 1 8 0 9 1 9 2 9 1 9 0 9 1 9 0 10 0 A表和B表的ID是相等的 2、求: 当传入ID等于1的时候，我要算出它和它子集的4条状态统计数据, a.开机到关机是多少次 b.开机到待机是多少次 c.待机到关机是多少次 d待机到开机是多少次 关键问题是B表里面只会有最底层的数据，然后传入要查询的参数ID不会是最底层的ID, 麻烦的问题是上一条数据不好查，哪位大神给个标值函数或存储过程 3、想要结果，当传入要查询的参数ID=1时， ID 　a b c d 1 5 1 0 1 2 3 0 0 0 3 2 1 0 1

create or replace procedure test(pi_start_num number) is
  l_count    number;
  l_state    number;
  l_to_state number;
  l_a        number;
  l_b        number;
  l_c        number;
  l_d        number;
begin
  dbms_output.put_line('id' || ' a' || ' b' || ' c' || ' d');
  for c1 in (select id
               from a
              start with id = pi_start_num
             connect by pid = prior id
                    and (level = 1 or level = 2)
              order by id) loop
    for c2 in (select 'a' typ
                 from dual
               union all
               select 'b' typ
                 from dual
               union all
               select 'c' typ
                 from dual
               union all
               select 'd' typ from dual) loop
      if c2.typ = 'a' then
        l_state    := 1;
        l_to_state := 0;
      elsif c2.typ = 'b' then
        l_state    := 1;
        l_to_state := 2;
      elsif c2.typ = 'c' then
        l_state    := 2;
        l_to_state := 0;
      else
        l_state    := 2;
        l_to_state := 1;
      end if;
    
      select count(*)
        into l_count
        from (select b1.id,
                     b1.state,
                     lead(state) over(partition by b1.id order by rn) to_state
                from (select b.*, rownum rn from b) b1,
                     (select *
                        from a
                       start with id = c1.id
                      connect by pid = prior id) a1
               where b1.id = a1.id)
       where state = l_state
         and to_state = l_to_state;
    
      if c2.typ = 'a' then
        l_a := l_count;
      elsif c2.typ = 'b' then
        l_b := l_count;
      elsif c2.typ = 'c' then
        l_c := l_count;
      else
        l_d := l_count;
      end if;
    
    end loop;
    dbms_output.put_line(c1.id || ' ' || l_a || ' ' || l_b || ' ' || l_c || ' ' || l_d);
  end loop;
end test;