求教一个视图的写法,该怎么处理

2014-06-01

求教一个视图的写法有如下一张数据表：create table cfg_callerrule_info(policy_id varchar(32),--策略IDr

求教一个视图的写法
有如下一张数据表：
create table cfg_callerrule_info
(
policy_id varchar(32),         --策略ID
rule_type smallint,            --1主叫挂断次数，2主叫接通率
time_slice smallint,           --时间片，单位为分钟，判断该时间段内，操作的情况
threshold varchar(20),         --违规发送频率阈值（对于主叫接通率，为百分数）
relation smallint,             --1：与，0：或，主叫挂断次数和主叫连通率的关系
primary key (policy_id,rule_type)
);
目前需要定义一个视图，需求如下：
(1)既有主叫挂断次数，又有主叫接通率的规则
policy_id   PN036201207181328000315
rule_type   1
time_slice  10
threshold   10
relation    1

policy_id   PN036201207181328000315
rule_type   2
time_slice  20
threshold   20
relation    1
得到：
policy_id,           PN036201207181328000315
hang_up_time_slice   10
hang_up_threshold    10
conn_time_slice      20
conn_threshold       20
relation             1
（2）只有主叫挂断次数、主叫接通率之一的策略
..
policy_id   PN036201207131503570103
rule_type   1
time_slice  22
threshold   22
relation    1
得到：
policy_id,           PN036201207131503570103
hang_up_time_slice   22
hang_up_threshold    22
conn_time_slice
conn_threshold
relation
..
policy_id   PN036201207181328000309
rule_type   2
time_slice  100
threshold   100
relation    1
得到
policy_id,           PN036201207131503570103
hang_up_time_slice
hang_up_threshold
conn_time_slice       100
conn_threshold        100
relation

现在写出来的视图只能满足(1),修改多次也没有达到（1）（2）都满足，
满足（1）的视图的写法：


CREATE VIEW v_cfg_callerrule_info(policy_id,hang_up_time_slice,hang_up_threshold,conn_time_slice,conn_threshold,relation)
AS
SELECT c1.policy_id,c1.time_slice,c1.threshold,c2.time_slice,c2.threshold,
CASE 
WHEN(c1.relation IS NOT NULL) THEN c1.relation
WHEN(c2.relation IS NOT NULL) THEN c2.relation
ELSE NULL
END

from cfg_callerrule_info c1,cfg_callerrule_info c2
where c1.policy_id = c2.policy_id and c1.rule_type < c2.rule_type

这个问题已经纠结了很久了，请各位不吝赐教！



[解决办法]
create view xxx as
with
  a as (select * from cfg_callerrule_info where rule_type=1),
  b as (select * from cfg_callerrule_info where rule_type=2)
select nvl(a.policy_id,b.policy_id) policy_id,a.time_slice,a.threshold,b.time_slice,b.threshold,
       (case when a.rule_type+b.rule_type=3 then 1 end)
from a full join b on a.policy_id=b.policy_id;
[解决办法]

CREATE VIEW v_cfg_callerrule_info(policy_id,hang_up_time_slice,hang_up_threshold,conn_time_slice,conn_threshold,relation) AS
SELECT c1.policy_id,
       c1.time_slice,
       c1.threshold,
       c2.time_slice,
       c2.threshold,
       CASE
         WHEN (c1.relation IS NOT NULL) THEN
          c1.relation
         WHEN (c2.relation IS NOT NULL) THEN
          c2.relation
         ELSE
          NULL
       END
  from cfg_callerrule_info c1, cfg_callerrule_info c2
where c1.policy_id = c2.policy_id
   and c1.rule_type < c2.rule_type
union
select c1.policy_id,
       c1.time_slice,
       c1.threshold,
       c2.time_slice,
       c2.threshold,

       c1.relatio
  from cfg_callerrule_info t1
where t1.policy_id in (select t1.policy_id
                          from cfg_callerrule_info t1
                         where t1.rule_type in (1, 2)
                         group by t1.policy_id
                        having count(rule_type) < 2) t2

热点排行

oracle

求教一个视图的写法,该怎么处理