sql 新需求

Question

一张表有daily_key,line,Mac，hour,status四个栏位， 
daily_key 日期， 
line有五跳线分别是：aa，bb，cc，dd，ee
每个line下可以对应多个Mac机器 
机器有两种状态RUN和DOWN
hour记录的是每种状态的时间。
由于用户每次输入的日期和需要查看的line不同，还要考虑到除数不能为零 ,如果为零则输出0，否则输出商的结果。
这句SQL是写在存储过程中;
需要输出 如下结果：
CREATE TABLE table_a
(  
daily_key  VARCHAR2(20),
line  VARCHAR2(20),
Mac  VARCHAR2(20),
hour   VARCHAR2(20),
status  VARCHAR2(20)
)
INSERT INTO table_a VALUES('20090909','aa','RUN','3','2');
INSERT INTO table_a VALUES('20090909','aa','RUN','1','1');
INSERT INTO table_a VALUES('20090909','aa','RUN','0','0');
INSERT INTO table_a VALUES('20090909','aa','DOWN','5','5');
INSERT INTO table_a VALUES('20090909','aa','DOWN','6','6');
INSERT INTO table_a VALUES('20090910','bb','RUN','1','2');
INSERT INTO table_a VALUES('20090910','bb','DOWN','5','4');
INSERT INTO table_a VALUES('20090910','bb','DOWN','6','8');
INSERT INTO table_a VALUES('20090910','bb','DOWN','21','11');
INSERT INTO table_a VALUES('20090910','bb','DOWN','33','11');
INSERT INTO table_a VALUES('20090910','cc','DOWN','10','20');
INSERT INTO table_a VALUES('20090910','cc','DOWN','50','40');
INSERT INTO table_a VALUES('20090910','cc','DOWN','60','80');
INSERT INTO table_a VALUES('20090910','cc','DOWN','21','11');比如说line   aa;     
    aa上所有Mac机器RUN的时间除以aa上所有Mac机器DOWN的时间
  
日期                       aa                      bb                    cc                     ee                    ff
20090101      RUN/DOWN  RUN/DOWN    RUN/DOWN  RUN/DOWN    RUN/DOWN
20090102      RUN/DOWN  RUN/DOWN    RUN/DOWN  RUN/DOWN    RUN/DOWN
20090103      RUN/DOWN  RUN/DOWN    RUN/DOWN  RUN/DOWN    RUN/DOWN
日期            aa         bb        
20090101      RUN/DOWN  RUN/DOWN   
20090102      RUN/DOWN  RUN/DOWN    
20090103      RUN/DOWN  RUN/DOWN

Answer 1

daily_key  VARCHAR2(20), 
line  VARCHAR2(20), 
Mac  VARCHAR2(20), 
hour  VARCHAR2(20), 
status  VARCHAR2(20) 
后面两个字段的含义是什么呀？解释一下各个字段什么含义。

Answer 2

status 代表两种状态RUN和DOWN
hour 是代表每种状态持续的时间。

Answer 3

SQL> select * from table_a;DAILY_KEY            LINE       MAC        HOUR       STATUS                    
-------------------- ---------- ---------- ---------- ----------                
20090909             aa         a1         2          RUN                       
20090909             aa         a1         1          RUN                       
20090909             aa         a1         0          RUN                       
20090909             aa         a2         5          DOWN                      
20090909             aa         a2         6          DOWN                      
20090910             bb         b1         2          RUN                       
20090910             bb         b2         4          DOWN                      
20090910             bb         b3         8          DOWN                      
20090910             bb         b4         11         DOWN                      
20090910             bb         b4         11         DOWN                      
20090910             cc         c1         20         DOWN                      DAILY_KEY            LINE       MAC        HOUR       STATUS                    
-------------------- ---------- ---------- ---------- ----------                
20090910             cc         c1         40         DOWN                      
20090910             cc         c1         80         DOWN                      
20090910             cc         c1         11         DOWN                      已选择14行。SQL> select daily_key 日期,max(decode(line,'aa',decode(down,0,'0',run||'/'||down))) "aa"
  2  ,max(decode(line,'bb',decode(down,0,'0',run||'/'||down))) "bb"
  3  ,max(decode(line,'cc',decode(down,0,'0',run||'/'||down))) "cc"
  4  ,max(decode(line,'dd',decode(down,0,'0',run||'/'||down))) "dd"
  5  ,max(decode(line,'ee',decode(down,0,'0',run||'/'||down))) "ee"
  6  from (select daily_key,line,sum(decode(status,'RUN',hour,0)) run,sum(decode(status,'DOWN',hour,0)) down
  7  from table_a group by daily_key,line)
  8  group by daily_key
  9  order by 1;日期                 aa         bb         cc         dd         ee             
-------------------- ---------- ---------- ---------- ---------- ----------     
20090909             3/11                                                       
20090910                        2/34       0/151 

Answer 4

如果动态产生列的话，那就参照我在上个帖子中给你回的那个过程，通过动态SQL创建个视图