如何优化下面的select语句?

Question

select timestamp as Time, (eventtype) as Event, 
(decode(eventtype,'Open',decode(num2,0,'Ag',1,'Ch',3,'QA',4,'Share',5,'Tele',6,'Doc',8,'Po',10,'File',11,'Vid',12,'VoI',18,'SSR',20,'MMP',21,' MMPo',50,'NBR_2',30,'Other',40,'Other2')
       ,'Close',decode(num2,0,'Ag',1,'Ch',3,'QA',4,'Share',5,'Tele',6,'Doc',8,'Po',10,'File',11,'Vid',12,'VoI',18,'SSR',20,'MMP',21,' MMPo',50,'NBR_2',30,'Other',40,'Other2')
    ,'Action',decode(num4,1,'Host',2,'Presenter',10,'Disconnect/Rejoin',20,'Terminate',100,'Meeting Locked',101,'Meeting Unlocked')
    ,'UserJoin',decode(num2,1,'Ch',3,'QA',4,'Share',5,'Tele',6,'Doc',8,'Po',10,'File',11,'Vid',12,'VoI',18,'SSR',20,'MMP',21,' MMPo',50,'NBR_2',30,'Other',40,'Other2')
    ,'UserLeave',decode(num2,1,'Ch',3,'QA',4,'Share',5,'Tele',6,'Doc',8,'Po',10,'File',11,'Vid',12,'VoI',18,'SSR',20,'MMP',21,' MMPo',50,'NBR_2',30,'Other',40,'Other2')
    ,'Join','join'
    ,'tJoin','join'
    ,'Leave','leave'
    ,'tLeave','leave')) as Description, 
    
(decode(eventtype,'Prepare',str3,'Open',str3,'Close',(select str3 from wbxlog b where b.id=a.id and b.eventtype='Open' and b.sequencenumber=a.sequencenumber),'UserJoin',decode(num2,0,str1),'UserLeave',(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num3 and c.sequencenumber=a.sequencenumber)))) as Name,(decode(eventtype,'Open',substr(a.STR1,instr(a.STR1,'/', 1, 2)+1, instr(a.STR1,'.', 1, 1)-instr(a.STR1,'/', 1, 2)-1)
                 ,'Join',str1
     ,'tJoin',substr(a.STR1,instr(a.STR1,'/', 1, 2)+1, instr(a.STR1,'.', 1, 1)-instr(a.STR1,'/', 1, 2)-1)
     ,'Leave',str1
     ,'tLeave',substr(a.STR1,instr(a.STR1,'/', 1, 2)+1, instr(a.STR1,'.', 1, 1)-instr(a.STR1,'/', 1, 2)-1) 
     ,'RecordingStart',substr(a.STR1,instr(a.STR1,'/', 1, 2)+1, instr(a.STR1,'.', 1, 1)-instr(a.STR1,'/', 1, 2)-1)
     ,'RecordingStop',substr(a.STR1,instr(a.STR1,'/', 1, 2)+1, instr(a.STR1,'.', 1, 1)-instr(a.STR1,'/', 1, 2)-1)
     ,'RecordingFail',substr(a.STR1,instr(a.STR1,'/', 1, 2)+1, instr(a.STR1,'.', 1, 1)-instr(a.STR1,'/', 1, 2)-1))) as IP,(decode(eventtype,'UserJoin',decode(num2,0,(select type from PLATFORM_TYPE where (substr(a.STR3,1, instr(a.STR3,',', 1, 1)-1))=id)))) as Platform,(decode(eventtype,'UserJoin',decode(num2,0,(select type from BROWSE_TYPE where (substr(a.STR3,(instr(a.STR3,',', 1, 1)+1), (instr(a.STR3,',', 1, 2))-(instr(a.STR3,',', 1, 1))-1))=id)))) as Browse,(decode(eventtype,'Action',decode(num2, 0,decode(num3,0,decode(num4,1,
(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num5 and c.sequencenumber=a.sequencenumber)),
2,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num5 and c.sequencenumber=a.sequencenumber)),
10,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num5 and c.sequencenumber=a.sequencenumber)),
20,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num5 and c.sequencenumber=a.sequencenumber)),
100,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num5 and c.sequencenumber=a.sequencenumber)),
101,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num5 and c.sequencenumber=a.sequencenumber))))))) as From,(decode(eventtype,'Action',decode(num2, 0,decode(num3,0,decode(num4,1,
(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num6 and c.sequencenumber=a.sequencenumber)),
2,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num6 and c.sequencenumber=a.sequencenumber)),
10,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num6 and c.sequencenumber=a.sequencenumber)),
20,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num6 and c.sequencenumber=a.sequencenumber)),
100,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num6 and c.sequencenumber=a.sequencenumber)),
101,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num6 and c.sequencenumber=a.sequencenumber))))))) as To,(decode(eventtype,'UserLeave',decode(num2,0,(select DESCRIPTION from USER_LEAVE_REASON where (num6)=REASONCODE )))) as Reason,id from wbxlog a order by id, timestamp

Answer 1

难得，这SQL够乱的。
需要看明白不容易，失去耐心。。

Answer 2

select timestamp as Time, (eventtype) as Event, (decode(eventtype,'Action',decode(num2, 0,decode(num3,0,decode(num4,1,
(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num6 and c.sequencenumber=a.sequencenumber)),
2,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num6 and c.sequencenumber=a.sequencenumber)),
10,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num6 and c.sequencenumber=a.sequencenumber)),
20,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num6 and c.sequencenumber=a.sequencenumber)),
100,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num6 and c.sequencenumber=a.sequencenumber)),
101,(select str1 from (select id, eventtype, num2, num3, num4, num5, str1, sequencenumber, sequencenum2 from wbxlog where eventtype='UserJoin' and num2=0) c 
where (c.id=a.id and c.num3=a.num6 and c.sequencenumber=a.sequencenumber))))))) as To,(decode(eventtype,'UserLeave',decode(num2,0,(select DESCRIPTION from USER_LEAVE_REASON where (num6)=REASONCODE )))) as Reason from wbxlog a order by id, timestamp
主要是decode的条件有多个,而且每个条件的结果都是一条select语句,如何优化decode和select语句,或者是否还有更好的方法?

Answer 3

你那里面用到两个关键字了,from ,to 我改成了f,tSELECT   TIMESTAMP AS TIME, (eventtype) AS event,
         (DECODE (eventtype,
                  'Open', DECODE (num2,
                                  0, 'Ag',
                                  1, 'Ch',
                                  3, 'QA',
                                  4, 'Share',
                                  5, 'Tele',
                                  6, 'Doc',
                                  8, 'Po',
                                  10, 'File',
                                  11, 'Vid',
                                  12, 'VoI',
                                  18, 'SSR',
                                  20, 'MMP',
                                  21, ' MMPo',
                                  50, 'NBR_2',
                                  30, 'Other',
                                  40, 'Other2'
                                 ),
                  'Close', DECODE (num2,
                                   0, 'Ag',
                                   1, 'Ch',
                                   3, 'QA',
                                   4, 'Share',
                                   5, 'Tele',
                                   6, 'Doc',
                                   8, 'Po',
                                   10, 'File',
                                   11, 'Vid',
                                   12, 'VoI',
                                   18, 'SSR',
                                   20, 'MMP',
                                   21, ' MMPo',
                                   50, 'NBR_2',
                                   30, 'Other',
                                   40, 'Other2'
                                  ),
                  'Action', DECODE (num4,
                                    1, 'Host',
                                    2, 'Presenter',
                                    10, 'Disconnect/Rejoin',
                                    20, 'Terminate',
                                    100, 'Meeting Locked',
                                    101, 'Meeting Unlocked'
                                   ),
                  'UserJoin', DECODE (num2,
                                      1, 'Ch',
                                      3, 'QA',
                                      4, 'Share',
                                      5, 'Tele',
                                      6, 'Doc',
                                      8, 'Po',
                                      10, 'File',
                                      11, 'Vid',
                                      12, 'VoI',
                                      18, 'SSR',
                                      20, 'MMP',
                                      21, ' MMPo',
                                      50, 'NBR_2',
                                      30, 'Other',
                                      40, 'Other2'
                                     ),
                  'UserLeave', DECODE (num2,
                                       1, 'Ch',
                                       3, 'QA',
                                       4, 'Share',
                                       5, 'Tele',
                                       6, 'Doc',
                                       8, 'Po',
                                       10, 'File',
                                       11, 'Vid',
                                       12, 'VoI',
                                       18, 'SSR',
                                       20, 'MMP',
                                       21, ' MMPo',
                                       50, 'NBR_2',
                                       30, 'Other',
                                       40, 'Other2'
                                      ),
                  'Join', 'join',
                  'tJoin', 'join',
                  'Leave', 'leave',
                  'tLeave', 'leave'
                 )
         ) AS description,

Answer 4

         (DECODE (eventtype,
                  'Prepare', str3,
                  'Open', str3,
                  'Close', (SELECT str3
                              FROM wbxlog b
                             WHERE b.ID = a.ID
                               AND b.eventtype = 'Open'
                               AND b.sequencenumber = a.sequencenumber),
                  'UserJoin', DECODE (num2, 0, str1),
                  'UserLeave', (SELECT str1
                                  FROM (SELECT ID, eventtype, num2, num3,
                                               num4, num5, str1,
                                               sequencenumber, sequencenum2
                                          FROM wbxlog
                                         WHERE eventtype = 'UserJoin'
                                           AND num2 = 0) c
                                 WHERE (    c.ID = a.ID
                                        AND c.num3 = a.num3
                                        AND c.sequencenumber =
                                                              a.sequencenumber
                                       ))
                 )
         ) AS NAME,
         (DECODE (eventtype,
                  'Open', SUBSTR (a.str1,
                                  INSTR (a.str1, '/', 1, 2) + 1,
                                    INSTR (a.str1, '.', 1, 1)
                                  - INSTR (a.str1, '/', 1, 2)
                                  - 1
                                 ),
                  'Join', str1,
                  'tJoin', SUBSTR (a.str1,
                                   INSTR (a.str1, '/', 1, 2) + 1,
                                     INSTR (a.str1, '.', 1, 1)
                                   - INSTR (a.str1, '/', 1, 2)
                                   - 1
                                  ),
                  'Leave', str1,
                  'tLeave', SUBSTR (a.str1,
                                    INSTR (a.str1, '/', 1, 2) + 1,
                                      INSTR (a.str1, '.', 1, 1)
                                    - INSTR (a.str1, '/', 1, 2)
                                    - 1
                                   ),
                  'RecordingStart', SUBSTR (a.str1,
                                            INSTR (a.str1, '/', 1, 2) + 1,
                                              INSTR (a.str1, '.', 1, 1)
                                            - INSTR (a.str1, '/', 1, 2)
                                            - 1
                                           ),
                  'RecordingStop', SUBSTR (a.str1,
                                           INSTR (a.str1, '/', 1, 2) + 1,
                                             INSTR (a.str1, '.', 1, 1)
                                           - INSTR (a.str1, '/', 1, 2)
                                           - 1
                                          ),
                  'RecordingFail', SUBSTR (a.str1,
                                           INSTR (a.str1, '/', 1, 2) + 1,
                                             INSTR (a.str1, '.', 1, 1)
                                           - INSTR (a.str1, '/', 1, 2)
                                           - 1
                                          )
                 )
         ) AS ip,

Answer 5

(DECODE (eventtype,
                  'UserJoin', DECODE (num2,
                                      0, (SELECT TYPE
                                            FROM platform_type
                                           WHERE (SUBSTR (a.str3,
                                                          1,
                                                            INSTR (a.str3,
                                                                   ',',
                                                                   1,
                                                                   1
                                                                  )
                                                          - 1
                                                         )
                                                 ) = ID)
                                     )
                 )
         ) AS platform,
         (DECODE (eventtype,
                  'UserJoin', DECODE (num2,
                                      0, (SELECT TYPE
                                            FROM browse_type
                                           WHERE (SUBSTR (a.str3,
                                                          (  INSTR (a.str3,
                                                                    ',',
                                                                    1,
                                                                    1
                                                                   )
                                                           + 1
                                                          ),
                                                            (INSTR (a.str3,
                                                                    ',',
                                                                    1,
                                                                    2
                                                                   )
                                                            )
                                                          - (INSTR (a.str3,
                                                                    ',',
                                                                    1,
                                                                    1
                                                                   )
                                                            )
                                                          - 1
                                                         )
                                                 ) = ID)
                                     )
                 )
         ) AS browse,

Answer 6

         (DECODE (eventtype,
                  'Action', DECODE
                            (num2,
                             0, DECODE
                                    (num3,
                                     0, DECODE
                                           (num4,
                                            1, (SELECT str1
                                                  FROM (SELECT ID, eventtype,
                                                               num2, num3,
                                                               num4, num5,
                                                               str1,
                                                               sequencenumber,
                                                               sequencenum2
                                                          FROM wbxlog
                                                         WHERE eventtype =
                                                                    'UserJoin'
                                                           AND num2 = 0) c
                                                 WHERE (    c.ID = a.ID
                                                        AND c.num3 = a.num5
                                                        AND c.sequencenumber =
                                                               a.sequencenumber
                                                       )),
                                            2, (SELECT str1
                                                  FROM (SELECT ID, eventtype,
                                                               num2, num3,
                                                               num4, num5,
                                                               str1,
                                                               sequencenumber,
                                                               sequencenum2
                                                          FROM wbxlog
                                                         WHERE eventtype =
                                                                    'UserJoin'
                                                           AND num2 = 0) c
                                                 WHERE (    c.ID = a.ID
                                                        AND c.num3 = a.num5
                                                        AND c.sequencenumber =
                                                               a.sequencenumber
                                                       )),
                                            10, (SELECT str1
                                                   FROM (SELECT ID, eventtype,
                                                                num2, num3,
                                                                num4, num5,
                                                                str1,
                                                                sequencenumber,
                                                                sequencenum2
                                                           FROM wbxlog
                                                          WHERE eventtype =
                                                                    'UserJoin'
                                                            AND num2 = 0) c
                                                  WHERE (    c.ID = a.ID
                                                         AND c.num3 = a.num5
                                                         AND c.sequencenumber =
                                                                a.sequencenumber
                                                        )),
                                            20, (SELECT str1
                                                   FROM (SELECT ID, eventtype,
                                                                num2, num3,
                                                                num4, num5,
                                                                str1,
                                                                sequencenumber,
                                                                sequencenum2
                                                           FROM wbxlog
                                                          WHERE eventtype =
                                                                    'UserJoin'
                                                            AND num2 = 0) c
                                                  WHERE (    c.ID = a.ID
                                                         AND c.num3 = a.num5
                                                         AND c.sequencenumber =
                                                                a.sequencenumber
                                                        )),
                                            100, (SELECT str1
                                                    FROM (SELECT ID,
                                                                 eventtype,
                                                                 num2, num3,
                                                                 num4, num5,
                                                                 str1,
                                                                 sequencenumber,
                                                                 sequencenum2
                                                            FROM wbxlog
                                                           WHERE eventtype =
                                                                    'UserJoin'
                                                             AND num2 = 0) c
                                                   WHERE (    c.ID = a.ID
                                                          AND c.num3 = a.num5
                                                          AND c.sequencenumber =
                                                                 a.sequencenumber
                                                         )),
                                            101, (SELECT str1
                                                    FROM (SELECT ID,
                                                                 eventtype,
                                                                 num2, num3,
                                                                 num4, num5,
                                                                 str1,
                                                                 sequencenumber,
                                                                 sequencenum2
                                                            FROM wbxlog
                                                           WHERE eventtype =
                                                                    'UserJoin'
                                                             AND num2 = 0) c
                                                   WHERE (    c.ID = a.ID
                                                          AND c.num3 = a.num5
                                                          AND c.sequencenumber =
                                                                 a.sequencenumber
                                                         ))
                                           )
                                    )
                            )
                 )
         ) AS f,

Answer 7

(DECODE (eventtype,
                  'Action', DECODE
                            (num2,
                             0, DECODE
                                    (num3,
                                     0, DECODE
                                           (num4,
                                            1, (SELECT str1
                                                  FROM (SELECT ID, eventtype,
                                                               num2, num3,
                                                               num4, num5,
                                                               str1,
                                                               sequencenumber,
                                                               sequencenum2
                                                          FROM wbxlog
                                                         WHERE eventtype =
                                                                    'UserJoin'
                                                           AND num2 = 0) c
                                                 WHERE (    c.ID = a.ID
                                                        AND c.num3 = a.num6
                                                        AND c.sequencenumber =
                                                               a.sequencenumber
                                                       )),
                                            2, (SELECT str1
                                                  FROM (SELECT ID, eventtype,
                                                               num2, num3,
                                                               num4, num5,
                                                               str1,
                                                               sequencenumber,
                                                               sequencenum2
                                                          FROM wbxlog
                                                         WHERE eventtype =
                                                                    'UserJoin'
                                                           AND num2 = 0) c
                                                 WHERE (    c.ID = a.ID
                                                        AND c.num3 = a.num6
                                                        AND c.sequencenumber =
                                                               a.sequencenumber
                                                       )),
                                            10, (SELECT str1
                                                   FROM (SELECT ID, eventtype,
                                                                num2, num3,
                                                                num4, num5,
                                                                str1,
                                                                sequencenumber,
                                                                sequencenum2
                                                           FROM wbxlog
                                                          WHERE eventtype =
                                                                    'UserJoin'
                                                            AND num2 = 0) c
                                                  WHERE (    c.ID = a.ID
                                                         AND c.num3 = a.num6
                                                         AND c.sequencenumber =
                                                                a.sequencenumber
                                                        )),
                                            20, (SELECT str1
                                                   FROM (SELECT ID, eventtype,
                                                                num2, num3,
                                                                num4, num5,
                                                                str1,
                                                                sequencenumber,
                                                                sequencenum2
                                                           FROM wbxlog
                                                          WHERE eventtype =
                                                                    'UserJoin'
                                                            AND num2 = 0) c
                                                  WHERE (    c.ID = a.ID
                                                         AND c.num3 = a.num6
                                                         AND c.sequencenumber =
                                                                a.sequencenumber
                                                        )),
                                            100, (SELECT str1
                                                    FROM (SELECT ID,
                                                                 eventtype,
                                                                 num2, num3,
                                                                 num4, num5,
                                                                 str1,
                                                                 sequencenumber,
                                                                 sequencenum2
                                                            FROM wbxlog
                                                           WHERE eventtype =
                                                                    'UserJoin'
                                                             AND num2 = 0) c
                                                   WHERE (    c.ID = a.ID
                                                          AND c.num3 = a.num6
                                                          AND c.sequencenumber =
                                                                 a.sequencenumber
                                                         )),
                                            101, (SELECT str1
                                                    FROM (SELECT ID,
                                                                 eventtype,
                                                                 num2, num3,
                                                                 num4, num5,
                                                                 str1,
                                                                 sequencenumber,
                                                                 sequencenum2
                                                            FROM wbxlog
                                                           WHERE eventtype =
                                                                    'UserJoin'
                                                             AND num2 = 0) c
                                                   WHERE (    c.ID = a.ID
                                                          AND c.num3 = a.num6
                                                          AND c.sequencenumber =
                                                                 a.sequencenumber
                                                         ))
                                           )
                                    )
                            )
                 )
         ) AS t,

Answer 8

         (DECODE (eventtype,
                  'UserLeave', DECODE (num2, 0, (SELECT description
                                                   FROM user_leave_reason
                                                  WHERE (num6) = reasoncode))
                 )
         ) AS reason,
         ID
    FROM wbxlog a
ORDER BY ID, TIMESTAMP

Answer 9

谢谢, hebo2005. 这个语句是正确的,只是想如何优化性能,而不是格式.

Answer 10

或者说,不用decode,是不是还有更好的方法.

Answer 11

SQL不难，就是写的实在不敢苟同，给你建议：像以下的代码应该消灭，你在Select字段写查询，不是不可以，
这样的写法取决于数据量和用法，你想把要查询1000条记录，假如1000次就
可以解决，结构你这样写，Oracle完全没有优化可言,Oralce只能执行
1001次检索，其中的一次是主查询，其余全为Select字段查询，这样的SQL
能运行也是个奇迹，也从另一方面看出来你的数据不会很多，因为这样的SQL如果记录上万，
你必死无疑。看了程序意义，完全可以用关联进行求解。
---------------------------------------------------------
(DECODE (eventtype,
                  'Action', DECODE
                            (num2,
                             0, DECODE
                                    (num3,
                                     0, DECODE
                                           (num4,
                                            1, (SELECT str1
                                                  FROM (SELECT ID, eventtype,
                                                               num2, num3,
                                                               num4, num5,
                                                               str1,
                                                               sequencenumber,
                                                               sequencenum2
                                                          FROM wbxlog
                                                         WHERE eventtype =
                                                                    'UserJoin'
                                                           AND num2 = 0) c
                                                 WHERE (    c.ID = a.ID
                                                        AND c.num3 = a.num6
                                                        AND c.sequencenumber =
                                                               a.sequencenumber
                                                       )),

Answer 12

谢谢 ehuman, 可以把说说如何改进呢?

Answer 13

改造前的SQL语句：
-------------------------------------
select 
(DECODE (eventtype,
          'Prepare', str3,
          'Open', str3,
          'Close', (SELECT str3
                      FROM wbxlog b
                     WHERE b.ID = a.ID
                       AND b.eventtype = 'Open'
                       AND b.sequencenumber = a.sequencenumber),
          'UserJoin', DECODE (num2, 0, str1),
          'UserLeave', (SELECT str1
                          FROM (SELECT ID, eventtype, num2, num3,
                                       num4, num5, str1,
                                       sequencenumber, sequencenum2
                                  FROM wbxlog
                                 WHERE eventtype = 'UserJoin'
                                   AND num2 = 0) c
                         WHERE (    c.ID = a.ID
                                AND c.num3 = a.num3
                                AND c.sequencenumber = a.sequencenumber
                               ))
                 )
         ) AS NAME
FROM wbxlog a
ORDER BY ID, TIMESTAMP
修正后的(没有验证过，主要是显示方法)
-------------------------------------------------
select 
DECODE (eventtype,
          'Prepare', str3,
          'Open', str3,
          'Close', b.str3,
          'UserJoin', DECODE (num2, 0, str1),
          'UserLeave',C.str1
         ) AS NAME
FROM wbxlog a,
    (SELECT ID,sequencenumber,str3
       FROM wbxlog
      WHERE b.ID = a.ID
        AND b.eventtype = 'Open') B,
    (SELECT ID, eventtype, num2, num3,
            num4, num5, str1,
            sequencenumber, sequencenum2
       FROM wbxlog
      WHERE eventtype = 'UserJoin'
        AND num2 = 0) C
where c.ID(+) = a.ID
  AND c.num3(+) = a.num3
  AND c.sequencenumber(+) = a.sequencenumber
  AND b.ID(+) = a.ID
  AND b.sequencenumber(+) = a.sequencenumber
    
ORDER BY ID, TIMESTAMP
以下Select部分需要合并
---------------------------------------------------------
 1, (SELECT str1
                                                  FROM (SELECT ID, eventtype,
                                                               num2, num3,
                                                               num4, num5,
                                                               str1,
                                                               sequencenumber,
                                                               sequencenum2
                                                          FROM wbxlog
                                                         WHERE eventtype =
                                                                    'UserJoin'
                                                           AND num2 = 0) c
                                                 WHERE (    c.ID = a.ID
                                                        AND c.num3 = a.num6
                                                        AND c.sequencenumber =
                                                               a.sequencenumber
                                                       )),
                                            2, (SELECT str1
                                                  FROM (SELECT ID, eventtype,
                                                               num2, num3,
                                                               num4, num5,
                                                               str1,
                                                               sequencenumber,
                                                               sequencenum2
                                                          FROM wbxlog
                                                         WHERE eventtype =
                                                                    'UserJoin'
                                                           AND num2 = 0) c
                                                 WHERE (    c.ID = a.ID
                                                        AND c.num3 = a.num6
                                                        AND c.sequencenumber =
                                                               a.sequencenumber
                                                       )),等等，太多东西重复了，合并相同的ＳＱＬ。
希望上面的思路能够你帮助。