相对复杂的竖表转横表+动态时间比较

Question

表Aid   Number  Re
1     111     aaa
2     222     bbb
3     444     ccc
4     333     ddd
5     555     eee
-----------------------------------
表Bid   Number   dongzuo        time
1     111      one     2008-8-1 13:00:00
2     111      two     2008-8-1 14:00:00
3     111      two     2008-8-2 15:00:00
4     111      two     2008-8-3 13:00:005     222      one     2008-8-1 13:30:00
6     222      two     2008-8-1 14:00:007     333      two     2008-8-1 13:13:00
8     333      one     2008-8-1 13:30:00
9     333      two     2008-8-2 11:00:00
10    333      two     2008-8-3 15:00:0011    444      two     2008-8-1 12:00:00
12    444      one     2008-8-2 13:00:0013    555      two     2008-8-2 13:00:00
-----------------------------------------------
我需要得到一个结果集，得到每一个号码在one动作后面的第一个和最后一个two动作，如果只有一个two动作，作为第一个，如果没有one动作，那么什么都不显示。而且要竖表转横表，结果如下：Number Re one     one_time     first_two  first_two_time    last_two  last_two_time
111     aaa   one 2008-8-1 13:00:00  two     2008-8-1 14:00:00    two    2008-8-3 13:00:00
222     bbb   one 2008-8-1 13:30:00  two     2008-8-1 14:00:00                            
333     ddd   one 2008-8-1 13:30:00  two     2008-8-2 11:00:00    two    2008-8-3 15:00:00
444     ccc   one 2008-8-2 13:00:00                                                       
555     eee                                                                               111--中间的那个2008-8-2 15:00:00不显示
222--one之后只有一个two，作为第一个two
333--one之前的那个two不要
444--one之前的那个two不要
555--没有one，什么都不显示 

Answer 1

不能用存储过程呦！只能用select的

Answer 2

办法是有的，先对B表的每行纪录生成是两个字段，看是否是第一条或者最后条纪录，
然后用DECODE生成行列转换表，再和A表关联

Answer 3

/* Formatted on 2008/08/19 17:11 (Formatter Plus v4.8.8) */
WITH a AS
     (SELECT 1 ID, 111 "number", 'aaa' re
        FROM DUAL
      UNION ALL
      SELECT 2, 222, 'bbb'
        FROM DUAL
      UNION ALL
      SELECT 3, 444, 'ccc'
        FROM DUAL
      UNION ALL
      SELECT 4, 333, 'ddd'
        FROM DUAL
      UNION ALL
      SELECT 5, 555, 'eee'
        FROM DUAL),
     b AS
     (SELECT 1 ID, 111 "number", 'one' dongzuo,
             TO_DATE ('2008-8-1 13:00:00', 'yyyy-mm-dd hh24:mi:ss') TIME
        FROM DUAL
      UNION ALL
      SELECT 2, 111, 'two' dongzuo,
             TO_DATE ('2008-8-1 14:00:00', 'yyyy-mm-dd hh24:mi:ss')
        FROM DUAL
      UNION ALL
      SELECT 3, 111, 'two' dongzuo,
             TO_DATE ('2008-8-2 15:00:00', 'yyyy-mm-dd hh24:mi:ss')
        FROM DUAL
      UNION ALL
      SELECT 4, 111, 'two' dongzuo,
             TO_DATE ('2008-8-3 13:00:00', 'yyyy-mm-dd hh24:mi:ss')
        FROM DUAL
      UNION ALL
      SELECT 5, 222, 'one' dongzuo,
             TO_DATE ('2008-8-1 13:30:00', 'yyyy-mm-dd hh24:mi:ss')
        FROM DUAL
      UNION ALL
      SELECT 6, 222, 'two' dongzuo,
             TO_DATE ('2008-8-1 14:00:00', 'yyyy-mm-dd hh24:mi:ss')
        FROM DUAL
      UNION ALL
      SELECT 7, 333, 'two' dongzuo,
             TO_DATE ('2008-8-1 13:13:00', 'yyyy-mm-dd hh24:mi:ss')
        FROM DUAL
      UNION ALL
      SELECT 8, 333, 'one' dongzuo,
             TO_DATE ('2008-8-1 13:30:00', 'yyyy-mm-dd hh24:mi:ss')
        FROM DUAL
      UNION ALL
      SELECT 9, 333, 'two' dongzuo,
             TO_DATE ('2008-8-2 11:00:00', 'yyyy-mm-dd hh24:mi:ss')
        FROM DUAL
      UNION ALL
      SELECT 10, 333, 'two' dongzuo,
             TO_DATE ('2008-8-3 15:00:00', 'yyyy-mm-dd hh24:mi:ss')
        FROM DUAL
      UNION ALL
      SELECT 11, 444, 'two' dongzuo,
             TO_DATE ('2008-8-1 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
        FROM DUAL
      UNION ALL
      SELECT 12, 444, 'one' dongzuo,
             TO_DATE ('    2008-8-2 13:00:00', 'yyyy-mm-dd hh24:mi:ss')
        FROM DUAL
      UNION ALL
      SELECT 13, 555, 'two' dongzuo,
             TO_DATE ('    2008-8-2 13:00:00', 'yyyy-mm-dd hh24:mi:ss')
        FROM DUAL)
SELECT   a."number", a.re, bbb.one, bbb.one_time, bbb.first_two,
         bbb.first_two_time, bbb.last_two, bbb.last_two_time
    FROM a,
         (SELECT   bb."number" num,
                   MAX (DECODE (bb.dongzuo, 'one', bb.dongzuo, NULL)) one,
                   MAX (DECODE (bb.dongzuo,
                                'one', TO_CHAR (bb.TIME,
                                                'yyyy-mm-dd hh24:mi:ss'
                                               ),
                                NULL
                               )
                       ) one_time,
                   MAX (DECODE (bb.o_t,
                                1, NULL,
                                DECODE (bb.dongzuo, 'two', bb.dongzuo, NULL)
                               )
                       ) first_two,
                   MAX
                      (DECODE
                           (bb.o_t,
                            1, NULL,
                            DECODE (bb.dongzuo,
                                    'two', DECODE
                                            (bb.f_num,
                                             1, TO_CHAR
                                                      (bb.TIME,
                                                       'yyyy-mm-dd hh24:mi:ss'
                                                      ),
                                             NULL
                                            ),
                                    NULL
                                   )
                           )
                      ) first_two_time,
                   MAX (DECODE (bb.o_t,
                                1, NULL,
                                DECODE (bb.dongzuo,
                                        'two', DECODE (bb.l_num,
                                                       1, DECODE (bb.f_num,
                                                                  1, NULL,
                                                                  bb.dongzuo
                                                                 ),
                                                       NULL
                                                      )
                                       )
                               )
                       ) last_two,
                   MAX
                      (DECODE
                          (bb.o_t,
                           1, NULL,
                           DECODE
                              (bb.dongzuo,
                               'two', DECODE
                                    (bb.l_num,
                                     1, DECODE
                                             (bb.f_num,
                                              1, NULL,
                                              TO_CHAR (bb.TIME,
                                                       'yyyy-mm-dd hh24:mi:ss'
                                                      )
                                             ),
                                     NULL
                                    )
                              )
                          )
                      ) last_two_time
              FROM (SELECT b.*,
                           ROW_NUMBER () OVER (PARTITION BY b."number", b.dongzuo ORDER BY TIME)
                                                                        f_num,
                           ROW_NUMBER () OVER (PARTITION BY b."number", b.dongzuo ORDER BY TIME DESC)
                                                                        l_num,
                           ROW_NUMBER () OVER (PARTITION BY b."number" ORDER BY b.dongzuo)
                                                                          o_t
                      FROM b) bb
          GROUP BY bb."number") bbb
   WHERE a."number" = bbb.num(+)
ORDER BY 1结果
Row# number REMARK ONE ONE_TIME FIRST_TWO FIRST_TWO_TIME LAST_TWO LAST_TWO_TIME1 111 aaa one 2008-08-01 13:00:00 two 2008-08-01 14:00:00 two 2008-08-03 13:00:00
2 222 bbb one 2008-08-01 13:30:00 two 2008-08-01 14:00:00
3 333 ddd one 2008-08-01 13:30:00 two 2008-08-01 13:13:00 two 2008-08-03 15:00:00
4 444 ccc one 2008-08-02 13:00:00 two 2008-08-01 12:00:00
5 555 eee

Answer 4

SELECT 
a.Number,a.re,t.first_one_time,t.first_tow_time,decode(t.first_tow_time,t.last_tow_time,'',t.last_tow_time) as last_tow_time
from 
(
SELECT b.Number,b.dongzuo,
FIRST_VALUE (b.time) over ( partition by  b.Number  order by  b.dongzuo,b.time )  as first_one_time,
FIRST_VALUE (b.time) over ( partition by  b.Number  order by  b.dongzuo desc,b.time )  as first_tow_time,
FIRST_VALUE (b.time) over ( partition by  b.Number  order by  b.dongzuo desc,b.time desc )  as last_tow_time,
row_number() over ( partition by  b.Number,b.dongzuo  order by rownum )  as rm
from  表B b 
) t,表A a
where t.rm(+) = 1 and t.dongzuo(+) = 'one'
  and a.Number =t.Number(+)

Answer 5

上面有错误SELECT   a."number", a.re, bbb.one, bbb.one_time, bbb.first_two,
         bbb.first_two_time, bbb.last_two, bbb.last_two_time
    FROM a,
         (SELECT   bb."number" num,
                   MAX (DECODE (bb.dongzuo, 'one', bb.dongzuo, NULL)) one,
                   MAX (DECODE (bb.dongzuo,
                                'one', TO_CHAR (bb.TIME,
                                                'yyyy-mm-dd hh24:mi:ss'
                                               ),
                                NULL
                               )
                       ) one_time,
                   MAX (DECODE (bb.o_t,
                                1, NULL,
                                DECODE (bb.dongzuo, 'two', bb.dongzuo, NULL)
                               )
                       ) first_two,
                   MAX
                      (DECODE
                           (bb.o_t,
                            1, NULL,
                            DECODE (bb.dongzuo,
                                    'two', DECODE
                                            (bb.f_num,
                                             1, TO_CHAR
                                                      (bb.TIME,
                                                       'yyyy-mm-dd hh24:mi:ss'
                                                      ),
                                             NULL
                                            ),
                                    NULL
                                   )
                           )
                      ) first_two_time,
                   MAX (DECODE (bb.o_t,
                                1, NULL,
                                DECODE (bb.dongzuo,
                                        'two', DECODE (bb.l_num,
                                                       1, DECODE (bb.f_num,
                                                                  1, NULL,
                                                                  bb.dongzuo
                                                                 ),
                                                       NULL
                                                      )
                                       )
                               )
                       ) last_two,
                   MAX
                      (DECODE
                          (bb.o_t,
                           1, NULL,
                           DECODE
                              (bb.dongzuo,
                               'two', DECODE
                                    (bb.l_num,
                                     1, DECODE
                                             (bb.f_num,
                                              1, NULL,
                                              TO_CHAR (bb.TIME,
                                                       'yyyy-mm-dd hh24:mi:ss'
                                                      )
                                             ),
                                     NULL
                                    )
                              )
                          )
                      ) last_two_time
              FROM (SELECT b.*,
                           ROW_NUMBER () OVER (PARTITION BY b."number", b.dongzuo ORDER BY TIME)
                                                                        f_num,
                           ROW_NUMBER () OVER (PARTITION BY b."number", b.dongzuo ORDER BY TIME DESC)
                                                                        l_num,
                           ROW_NUMBER () OVER (PARTITION BY b."number" ORDER BY TIME,
                            b.dongzuo) o_t
                      FROM (SELECT c.*,
                                   ROW_NUMBER () OVER (PARTITION BY c."number" ORDER BY TIME,
                                    c.dongzuo) o_t_1
                              FROM b c) b
                     WHERE dongzuo || o_t_1 <> 'two1') bb
          GROUP BY bb."number") bbb
   WHERE a."number" = bbb.num(+)
ORDER BY 1
结果
Row# number REMARK ONE ONE_TIME FIRST_TWO FIRST_TWO_TIME LAST_TWO LAST_TWO_TIME1 111 aaa one 2008-08-01 13:00:00 two 2008-08-01 14:00:00 two 2008-08-03 13:00:00
2 222 bbb one 2008-08-01 13:30:00 two 2008-08-01 14:00:00
3 333 ddd one 2008-08-01 13:30:00 two 2008-08-02 11:00:00 two 2008-08-03 15:00:00
4 444 ccc one 2008-08-02 13:00:00
5 555 eee

Answer 6

不好意思，写错了
SELECT 
a.Number,a.re,t.first_one_time,t.first_tow_time,decode(t.first_tow_time,t.last_tow_time,'',t.last_tow_time) as last_tow_time
from 
(
SELECT b.Number,b.dongzuo,
FIRST_VALUE (b.time) over ( partition by  b.Number  order by  b.dongzuo,b.time )  as first_one_time,
FIRST_VALUE (decode(b.dongzuo,'two',b.time,'')) over ( partition by  b.Number  order by  b.dongzuo desc,b.time )  as first_tow_time,
FIRST_VALUE (decode(b.dongzuo,'two',b.time,'')) over ( partition by  b.Number  order by  b.dongzuo desc,b.time desc )  as last_tow_time,
row_number() over ( partition by  b.Number,b.dongzuo  order by rownum )  as rm
from  表B b 
) t,表A a
where t.rm(+) = 1 and t.dongzuo(+) = 'one'
  and a.Number =t.Number(+)

Answer 7

楼上的，你的语句最好去实际测试下结果
我用你的语句的测试结果
Row# Number REMARK FIRST_ONE_TIME FIRST_TOW_TIME LAST_TOW_TIME1 111 aaa 2008/8/1 13:00:00 2008/8/1 14:00:00 03-AUG-08
2 222 bbb 2008/8/1 13:30:00 2008/8/1 14:00:00
3 333 ddd 2008/8/1 13:30:00 2008/8/1 13:13:00 03-AUG-08
4 444 ccc 2008/8/2 13:00:00 2008/8/1 12:00:00
5 555 eee 还需要修正

Answer 8

另外，字段名用保留字是个不好的习惯，像number
写语句的时候很麻烦，我上面全是用""的

Answer 9

Try it ..
SQL> SELECT * FROM AAA;       SID        NUM REMARK
---------- ---------- ----------
         1        111 AAA
         2        222 BBB
         3        333 CCC
         4        444 DDD
         5        555 EEESQL> SELECT * FROM BBB;       SID        NUM ACTION     STIME
---------- ---------- ---------- -----------
         1        111 ONE        8/1/2008 1:
         2        111 TWO        8/1/2008 2:
         3        111 TWO        8/2/2008 3:
         4        111 TWO        8/3/2008 1:
         5        222 ONE        8/1/2008 1:
         6        222 TWO        8/1/2008 2:
         7        333 TWO        8/1/2008 1:
         8        333 ONE        8/1/2008 1:
         9        333 TWO        8/2/2008 11
        10        333 TWO        8/3/2008 3:
        11        444 TWO        8/1/2008 12
        12        444 ONE        8/2/2008 1:
        13        555 TWO        8/2/2008 1:13 rows selected
SQL> SELECT A.NUM,
  2         A.REMARK,
  3         C.ONE,
  4         C.ONE_TIME,
  5         DECODE(C.ONE,NULL,NULL,DECODE(SIGN(C.FIRST_TWO_TIME-C.ONE_TIME),-1,NULL,C.FIRST_TWO)) "FIRST_TWO",
  6         DECODE(C.ONE,NULL,NULL,DECODE(SIGN(C.FIRST_TWO_TIME-C.ONE_TIME),-1,NULL,C.FIRST_TWO_TIME)) "FIRST_TWO_TIME",
  7         DECODE(C.ONE,NULL,NULL,DECODE(C.FIRST_TWO_TIME,C.LAST_TWO_TIME,NULL,C.LAST_TWO)) "LAST_TWO",
  8         DECODE(C.ONE,NULL,NULL,DECODE(C.FIRST_TWO_TIME,C.LAST_TWO_TIME,NULL,C.LAST_TWO_TIME)) "LAST_TWO_TIME"
  9    FROM AAA A,
 10         (
 11          SELECT B.NUM,
 12                 MIN(DECODE(UPPER(ACTION),'ONE','ONE',NULL)) "ONE",
 13                 MIN(DECODE(UPPER(ACTION),'ONE',STIME,NULL)) "ONE_TIME",
 14                 MIN(DECODE(UPPER(ACTION),'TWO','TWO',NULL)) "FIRST_TWO",
 15                 MIN(DECODE(UPPER(ACTION),'TWO',STIME,NULL)) "FIRST_TWO_TIME",
 16                 MAX(DECODE(UPPER(ACTION),'TWO','TWO',NULL)) "LAST_TWO",
 17                 MAX(DECODE(UPPER(ACTION),'TWO',STIME,NULL)) "LAST_TWO_TIME"
 18            FROM BBB B
 19           WHERE NOT EXISTS (
 20                           SELECT 1
 21                             FROM BBB BB
 22                            WHERE B.NUM = BB.NUM
 23                              AND UPPER(BB.ACTION) = 'ONE'
 24                              AND B.STIME < BB.STIME
 25                            )
 26           GROUP BY B.NUM
 27         )C
 28   WHERE A.NUM = C.NUM(+);       NUM REMARK     ONE C.ONE_TIME                     FIRST_TWO FIRST_TWO_TIME      LAST_TWO LAST_TWO_TIME
---------- ---------- --- ------------------------------ --------- ------------------- -------- -------------------
       111 AAA        ONE 2008-08-01 13:00:00            TWO       2008-08-01 14:00:00 TWO      2008-08-03 13:00:00
       222 BBB        ONE 2008-08-01 13:30:00            TWO       2008-08-01 14:00:00          
       333 CCC        ONE 2008-08-01 13:30:00            TWO       2008-08-02 11:00:00 TWO      2008-08-03 15:00:00
       444 DDD        ONE 2008-08-02 13:00:00                                                   
       555 EEE                                                                                  SQL> 

Answer 10

谢谢mantisXF 和hebo2005，还有热心帮助的 kinlin ，但是你们的结果我都试验了，mantisXF 和hebo2005测试通过了，kinlin 的有一些问题，但是mantisXF 的有一点时间格式的问题，已经解决。但是mantisXF 和hebo2005的方法，效率都不高，执行起来比较慢，我已经加了索引，还是不快，可能因为我的数据库里的数据量比较大吧，表B大概有将近4千万的数据了。总之谢谢各位了。

Answer 11

如果对应的动作字段值one,two等都是小写的话，用下面的SQL试试看，然后dongzuo(action)字段建索引。
    SELECT A.NUM,
           A.REMARK,
           C.ONE,
           C.ONE_TIME,
           DECODE(C.ONE,NULL,NULL,DECODE(SIGN(C.FIRST_TWO_TIME-C.ONE_TIME),-1,NULL,C.FIRST_TWO)) "FIRST_TWO",
           DECODE(C.ONE,NULL,NULL,DECODE(SIGN(C.FIRST_TWO_TIME-C.ONE_TIME),-1,NULL,C.FIRST_TWO_TIME)) "FIRST_TWO_TIME",
           DECODE(C.ONE,NULL,NULL,DECODE(C.FIRST_TWO_TIME,C.LAST_TWO_TIME,NULL,C.LAST_TWO)) "LAST_TWO",
           DECODE(C.ONE,NULL,NULL,DECODE(C.FIRST_TWO_TIME,C.LAST_TWO_TIME,NULL,C.LAST_TWO_TIME)) "LAST_TWO_TIME"
      FROM AAA A,
          (
           SELECT B.NUM,
                  MIN(DECODE(ACTION,'one','one',NULL)) "ONE",
                  MIN(DECODE(ACTION,'one',STIME,NULL)) "ONE_TIME",
                  MIN(DECODE(ACTION,'two','two',NULL)) "FIRST_TWO",
                  MIN(DECODE(ACTION,'two',STIME,NULL)) "FIRST_TWO_TIME",
                  MAX(DECODE(ACTION,'two','two',NULL)) "LAST_TWO",
                  MAX(DECODE(ACTION,'two',STIME,NULL)) "LAST_TWO_TIME"
             FROM BBB B
            WHERE NOT EXISTS (
                            SELECT 1
                              FROM BBB BB
                             WHERE B.NUM = BB.NUM
                               AND BB.ACTION = 'one'
                               AND B.STIME < BB.STIME
                             )
            GROUP BY B.NUM
          )C
    WHERE A.NUM = C.NUM(+);
Try it ..

Answer 12

如果对应的动作字段值one,two等都是大写，能用你上面的SQL吗？跟大小写有什么关系吗？