求一条 ORACLE 更新语句，在线等。

Question

 KTAB
 
 KID KLOGDATE KVOL KDATE
 
 
 TEMP
 
 TCD TVOL TDATE
 
 KID 数据格式：KK12345
 TCD 数据格式：KK-12345
 
 要求：
 将KTAB表里，KID后5位与TCD后5位相同的数据，且KLOGDATE小于并且最接近TDATE的KVOL和KDATE字段更新为TVOL和TDATE

Answer 1

SELECT TCD, TVOL, TDATE, 
 (SELECT KID FROM (SELECT KID FROM KTAB
   WHERE SUBSTR(KID, LENGTH(KID)-4, 5) = SUBSTR(TEMP.TCD, LENGTH(TEMP.TCD)-4, 5)
   AND KDATE <= TEMP.TDATE ORDER BY ROWNUM DESC) WHERE ROWNUM = 1
   ) AS KID
  FROM KTAB
为什么这条语句查出来的KID都是一样的？实在不明白……

Answer 2

主要是  KLOGDATE小于并且最接近TDATE  这个条件怎么处理。。

Answer 3

KTAB 表：KID         KLOGDATE                 KVOL            KDATE 
KK01011     2009-02-06 10:29:00         
KK01011     2009-02-09 15:27:00
KK01011     2009-02-11 17:02:00
KK01012     2009-02-02 13:05:00
KK01012     2009-02-03 17:08:00
KK01012     2009-02-13 14:27:00
KK01012     2009-02-17 18:10:00
KK01015     2009-02-02 11:21:00
KK01015     2009-02-09 18:10:00
KK01015     2009-02-21 11:22:00-------------------------------------------------------------------TEMP 表：TCD         TVOL             TDATE 
KK-01011    100.02           2009-02-10  16:07:06
KK-01012    205.18           2009-02-11  10:55:21
KK-01012    211.22           2009-02-20  09:25:03
KK-01015    196.44           2009-02-13  18:12:21--------------------------------------------------------------------更新后 KTAB 表中的数据应该为：KID         KLOGDATE                 KVOL            KDATE 
KK01011     2009-02-06 10:29:00         
KK01011     2009-02-09 15:27:00      100.02          2009-02-10  16:07:06
KK01011     2009-02-11 17:02:00
KK01012     2009-02-02 13:05:00
KK01012     2009-02-03 17:08:00      205.18          2009-02-11  10:55:21
KK01012     2009-02-13 14:27:00
KK01012     2009-02-17 18:10:00      211.22          2009-02-20  09:25:03
KK01015     2009-02-02 11:21:00
KK01015     2009-02-09 18:10:00      196.44          2009-02-13  18:12:21
KK01015     2009-02-21 11:22:00-----------------------------------------------------------------------谢谢！

Answer 4

UPDATE ktab a
SET    (KVOL, KDATE) = (SELECT MIN(TVOL) keep(dense_rank FIRST ORDER BY tdate), MIN(TDATE)
                        FROM   temp b
                        WHERE  b.tdate >= a.KLOGDATE
                        AND    substr(b.tcd, -5) = substr(a.kid, -5))
WHERE  ROWID IN (SELECT MAX(a1.ROWID) keep(dense_rank LAST ORDER BY a1.KLOGDATE)
                 FROM   temp b1, ktab a1
                 WHERE  substr(b1.tcd, -5) = substr(a1.kid, -5)
                 AND    b1.tdate >= a1.KLOGDATE
                 GROUP  BY b1.ROWID);

Answer 5

搞定！
以下语句你执行看看：
update ktab a
   set (a.kvol, a.kdate) = (select x.tvol, x.tdate
                              from (select a.kid,
                                           a.klogdate,
                                           b.tvol,
                                           b.tdate,
                                           abs(b.tdate - a.klogdate) as flag,
                                           row_number() over(partition by b.tcd, rr order by(abs(b.tdate - a.klogdate))) as rnt,
                                           b.rr
                                      from ktab a,
                                           (select b.tcd,
                                                   b.tvol,
                                                   b.tdate,
                                                   row_number() over(partition by b.tcd order by b.tcd) as rr
                                              from temp b) b
                                     where substr(a.kid, 3, 5) =
                                           substr(b.tcd, 4, 5)) x
                             where x.rnt = 1
                               and a.kid = x.kid
                               and a.klogdate = x.klogdate)
 where exists (select 1
          from (select a.kid,
                       a.klogdate,
                       b.tvol,
                       b.tdate,
                       abs(b.tdate - a.klogdate) as flag,
                       row_number() over(partition by b.tcd, rr order by(abs(b.tdate - a.klogdate))) as rnt,
                       b.rr
                  from ktab a,
                       (select b.tcd,
                               b.tvol,
                               b.tdate,
                               row_number() over(partition by b.tcd order by b.tcd) as rr
                          from temp b) b
                 where substr(a.kid, 3, 5) = substr(b.tcd, 4, 5)) x
         where x.rnt = 1
           and a.kid = x.kid
           and a.klogdate = x.klogdate);

Answer 6

你这样写是有问题的！
b.tdate >= a.KLOGDATE不能这么判断
如果tdate大于klogdate，并且与klogdate更接近，则你这里就是错误的
例如ktab中kid:KK01011  klogdate:2009-02-11 17:02:00改为：2009-02-11 00:00:00
则你这个语句更新就是错误的！

Answer 7

后面的where rowid...不是过滤掉了吗？

Answer 8

貌似下面更简单些。SQL> select * from ktab;
 
KID          KLOGDATE               KVOL         KDATE
------------ ---------------------- ------------ ----------------------
KK01011      2009-2-6 10:29:00                   
KK01011      2009-2-9 15:27:00                   
KK01011      2009-2-11 17:02:00                  
KK01012      2009-2-2 13:05:00                   
KK01012      2009-2-3 17:08:00                   
KK01012      2009-2-13 14:27:00                  
KK01012      2009-2-17 18:10:00                  
KK01015      2009-2-2 11:21:00                   
KK01015      2009-2-9 18:10:00                   
KK01015      2009-2-21 11:22:00                  
 
10 rows selected
 
UPDATE /*+BYPASS_UJVC*/ --
       (SELECT A.KVOL, A.KDATE, A, B
        FROM   ktab a,
               (SELECT MAX(a1.ROWID) keep(dense_rank LAST ORDER BY a1.KLOGDATE) RID,
                       B1.TVOL A,
                       B1.TDATE B
                FROM   temp b1, ktab a1
                WHERE  substr(b1.tcd, -5) = substr(a1.kid, -5)
                AND    b1.tdate >= a1.KLOGDATE
                GROUP  BY b1.ROWID, B1.TVOL, B1.TDATE) B
        WHERE  A.ROWID = B.RID)
SET    KVOL = A, KDATE = B;
 
4 rows updated
 
SQL> select * from ktab;
 
KID          KLOGDATE               KVOL         KDATE
------------ ---------------------- ------------ ----------------------
KK01011      2009-2-6 10:29:00                   
KK01011      2009-2-9 15:27:00      100.02       2009-2-10 16:07:06
KK01011      2009-2-11 17:02:00                  
KK01012      2009-2-2 13:05:00                   
KK01012      2009-2-3 17:08:00      205.18       2009-2-11 10:55:21
KK01012      2009-2-13 14:27:00                  
KK01012      2009-2-17 18:10:00     211.22       2009-2-20 9:25:03
KK01015      2009-2-2 11:21:00                   
KK01015      2009-2-9 18:10:00      196.44       2009-2-13 18:12:21
KK01015      2009-2-21 11:22:00                  
 
10 rows selected

Answer 9

为什么在 SET (KVOL, KDATE) 的时候报KDATE标识符无效？
to heipi_silent2，你的语句也同样会报a.kdate标识符无效麻烦两位解释下，谢谢