求一句高难度SQL

Question

最好不要用FOR循环嵌套，数据量大，影响效率。
一张表有daily_key,Machines,KAI,GUAN四个栏位，
daily_key 日期，
Machines有五台编号分别是：1，2，3，4，5
机器有两种状态，每开启一次KAI数值加1，每关一次GUAN的数值加1.
由于用户每次输入的日期和需要查看的几台不同，还要考虑到除数不能为零
需要输出如下结果
 日期              1         2            3        4             5
20090101       GUAN/KAI   GUAN/KAI    GUAN/KAI   GUAN/KAI    GUAN/KAI
20090102       GUAN/KAI   GUAN/KAI    GUAN/KAI   GUAN/KAI    GUAN/KAI
20090103       GUAN/KAI   GUAN/KAI    GUAN/KAI   GUAN/KAI    GUAN/KAI  或者是 
  
   日期             1          2          4           5
20090101       GUAN/KAI   GUAN/KAI    GUAN/KAI    GUAN/KAI
20090102       GUAN/KAI   GUAN/KAI    GUAN/KAI    GUAN/KAI    
20090103       GUAN/KAI   GUAN/KAI    GUAN/KAI    GUAN/KAI   
20090104       GUAN/KAI   GUAN/KAI    GUAN/KAI    GUAN/KAI 
20090105       GUAN/KAI   GUAN/KAI    GUAN/KAI    GUAN/KAI 

Answer 1

16:26:31 scott@TUNGKONG> select * from tb;DAILY_KEY    MACHINES        KAI       GUAN
---------- ---------- ---------- ----------
20090101            1          3          2
20090101            2          3          2
20090101            3          2          2
20090101            4          0          0
20090101            5          0          0
20090102            1          0          0
20090102            2          0          0
20090102            3          1          1
20090102            4          2          1
20090102            5          2          1已选择10行。已用时间:  00: 00: 00.01
16:26:34 scott@TUNGKONG> select daily_key 日期,max(decode(machines,1,decode(kai,0,0,guan/kai))) "1"
16:26:38   2  ,max(decode(machines,2,decode(kai,0,0,guan/kai))) "2"
16:26:38   3  ,max(decode(machines,3,decode(kai,0,0,guan/kai))) "3"
16:26:38   4  ,max(decode(machines,4,decode(kai,0,0,guan/kai))) "4"
16:26:38   5  ,max(decode(machines,5,decode(kai,0,0,guan/kai))) "5"
16:26:38   6  from tb
16:26:38   7  group by daily_key
16:26:38   8  order by 1;日期                1          2          3          4          5
---------- ---------- ---------- ---------- ---------- ----------
20090101   .666666667 .666666667          1          0          0
20090102            0          0          1         .5         .5已用时间:  00: 00: 00.01

Answer 2

还有不明白KAI,GUAN这两个字段是怎么赋值的
每条记录的kai,guan有1个要为空吧？不为空的标记为1？
能不能给点原表中的数据说明下

Answer 3

select daily_key
,sum(decode(Machines,1,GUAN,0))||'/'||sum(decode(Machines,1,KAI,0))  mach1
,sum(decode(Machines,2,GUAN,0))||'/'||sum(decode(Machines,2,KAI,0))  mach2
,sum(decode(Machines,3,GUAN,0))||'/'||sum(decode(Machines,3,KAI,0))  mach3
,sum(decode(Machines,4,GUAN,0))||'/'||sum(decode(Machines,4,KAI,0))  mach4
,sum(decode(Machines,5,GUAN,0))||'/'||sum(decode(Machines,5,KAI,0))  mach5
group by daily_key
试试看吧。
另外，除数如果为0，怎么办你没说清楚，就没考虑。

Answer 4

16:32:39 scott@TUNGKONG> select daily_key 日期,max(decode(machines,1,decode(kai,0,'0',guan||'/'||kai))) "1"
16:32:42   2  ,max(decode(machines,2,decode(kai,0,'0',guan||'/'||kai))) "2"
16:32:42   3  ,max(decode(machines,3,decode(kai,0,'0',guan||'/'||kai))) "3"
16:32:42   4  ,max(decode(machines,4,decode(kai,0,'0',guan||'/'||kai))) "4"
16:32:42   5  ,max(decode(machines,5,decode(kai,0,'0',guan||'/'||kai))) "5"
16:32:42   6  from tb
16:32:42   7  group by daily_key
16:32:42   8  order by 1;日期       1          2          3          4          5
---------- ---------- ---------- ---------- ---------- ----------
20090101   2/3        2/3        2/2        0          0
20090102   0          0          1/1        1/2        1/2已用时间:  00: 00: 00.01

Answer 5

你这样做看的是全部，如果只看1、2呢，不能每次都手改SQL吧。

Answer 6

既然要求列名不固定，那就用动态SQL吧，根据你传入的参数来调整列的显示。。

Answer 7

SQL> select * from tb;DAILY_KEY          MACHINES        KAI       GUAN                               
---------------- ---------- ---------- ----------                               
20090101                  1          3          2                               
20090101                  2          3          2                               
20090101                  3          2          2                               
20090101                  4          0          0                               
20090101                  5          0          0                               
20090102                  1          0          0                               
20090102                  2          0          0                               
20090102                  3          1          1                               
20090102                  4          2          1                               
20090102                  5          2          1                               已选择10行。SQL> create or replace procedure pro_test(str varchar2,strName varchar2)
  2  as
  3   strSQL varchar2(2000);
  4  begin
  5   strSQL := 'create or replace view ' || strName || ' as select daily_key 日期';
  6   for i in 1..length(str) loop
  7   strSQL := strSQL || ',max(decode(machines,' || substr(str,i,1) || ',decode(kai,0,''0'',guan||''/''||kai))) "' || substr(str,i,1) || '"';
  8   end loop;
  9   strSQL := strSQL || ' from tb group by daily_key order by 1';
 10   execute immediate strSQL;
 11  end;
 12  /过程已创建。SQL> exec pro_test(1235,'view_test');PL/SQL 过程已成功完成。SQL> select * from view_test;日期             1          2          3          5                             
---------------- ---------- ---------- ---------- ----------                    
20090101         2/3        2/3        2/2        0                             
20090102         0          0          1/1        1/2                           SQL> exec pro_test(235,'view_test');PL/SQL 过程已成功完成。SQL> select * from view_test;日期             2          3          5                                        
---------------- ---------- ---------- ----------                               
20090101         2/3        2/2        0                                        
20090102         0          1/1        1/2                                      

Answer 8

一般遇到很棘手的问题，请往case字句和decode函数上想。