请教一个instr的问题，谢谢。

Question

表名：BF_AGT_LNP_CTRT_APPLY
数据：LOANID   CRAPAPPID                           AMT                OPEN_DT
1     R3300001006058#10001                10000.00             2011-08-01
2     R3300001006058#10002                20000.00             2011-08-03
3     R33000010060583333(1)               50000.00             2011-07-02
4     R3300001003232#10001                300.00               2011-06-01
5                                         12000.00             2011-03-01想得到的结果：                                                                     求这个值 SUMAMT  
1     R3300001006058#10001                10000.00             2011-08-01          30000.00
2     R3300001006058#10002                20000.00             2011-08-03          30000.00
3     R33000010060583333(1)               50000.00             2011-07-02          50000.00
4     R3300001003232#10001                300.00               2011-06-01          300.00
5                                         12000.00             2011-03-01          12000.00这个值的算法就是如果CRAPAPPID包含'#',则取
case when instr(a.corpappid,'#')>0 then substr(a.corpappid,1,length(a.corpappid)-4) else a.corpappid end
比如R3300001006058#10001，得到的结果就是R3300001006058#。
我想把能匹配 R3300001006058#的数据的AMT累加后生成SUMAMT这个值。            

Answer 1

啥问题？不就分下组吗select case when instr(a.corpappid,'#')>0 then substr(a.corpappid,1,length(a.corpappid)-4) else a.corpappid end,
sum(amt) 
from table 
group by case when instr(a.corpappid,'#')>0 then substr(a.corpappid,1,length(a.corpappid)-4) else a.corpappid end

Answer 2

如2楼所说，那结果是：
1 R3300001006058#1      30000.00 2011-08-01
2 R33000010060583333(1) 50000.00 2011-07-02
3 R3300001003232#1      300.00 2011-06-01
4                       12000.00 2011-03-01这不是我想要的结果。

Answer 3

回3楼
我想要客户号带'#'的，并且截取后4位相同客户号的，把amt累计起来，并保留它的原始客户号。

Answer 4

WITH bf_agt_lnp_ctrt_apply AS (
SELECT '1' loanid,'R3300001006058#10001' crapappid,10000.00 amt,'2011-08-01' open_dt FROM dual UNION ALL
SELECT '2' loanid,'R3300001006058#10002' crapappid,20000.00 amt,'2011-08-03' open_dt FROM dual UNION ALL
SELECT '3' loanid,'R33000010060583333(1)' crapappid,50000.00 amt,'2011-07-02' open_dt FROM dual UNION ALL
SELECT '4' loanid,'R3300001003232#10001' crapappid,300.00 amt,'2011-06-01' open_dt FROM dual
)
SELECT t.loanid,
       t.crapappid,
       t.amt,
       t.open_dt,
       SUM(t.amt) over(PARTITION BY 
                          CASE WHEN instr(t.crapappid,'#') > 0 
                               THEN substr(t.crapappid, 1, length(t.crapappid) - 5) 
                               ELSE t.crapappid 
                          END 
                          ) sumamt
  FROM bf_agt_lnp_ctrt_apply t
  ORDER BY 1

Answer 5

LOANID CRAPAPPID                    AMT OPEN_DT        SUMAMT
------ --------------------- ---------- ---------- ----------
1      R3300001006058#10001       10000 2011-08-01      30000
2      R3300001006058#10002       20000 2011-08-03      30000
3      R33000010060583333(1)      50000 2011-07-02      50000
4      R3300001003232#10001         300 2011-06-01        300

Answer 6

谢谢5楼，但实际应用中我少加了一个条件，麻烦你再教我一下。 
WITH bf_agt_lnp_ctrt_apply AS ( 
SELECT '1' loanid,'R3300001006058#10001' crapappid,10000.00 amt,'2011-08-01' open_dt ,'2011-06-01' pay_dt FROM dual UNION ALL 
SELECT '2' loanid,'R3300001006058#10002' crapappid,20000.00 amt,'2011-08-03' open_dt ,'2011-05-12' pay_dt FROM dual UNION ALL 
SELECT '3' loanid,'R3300001006058#10003' crapappid,50000.00 amt,'2011-07-02' open_dt ,'2010-03-12' pay_dt FROM dual UNION ALL 
SELECT '4' loanid,'R3300001003232#10001' crapappid,32000.00 amt,'2011-06-01' open_dt ,'2011-03-12' pay_dt FROM dual 
) 
select是要求所有open_dt>='2011-08-01'的数据，sum(amt)的要求是所有pay_dt>='2011-01-01'的数据，所以结果应该是： 
1  R3300001006058#10001   10000 2011-08-01 2011-06-01 80000 
2  R3300001006058#10002   20000 2011-08-03 2011-05-12 80000 
4  R3300001003232#10001   32000 2011-06-01 2011-03-12 32000 
先谢谢啦.......................................... 

Answer 7

你给的结果正确吗？
按照你的说明，应该是下面的结果：SQL> WITH bf_agt_lnp_ctrt_apply AS (
  2  SELECT '1' loanid,'R3300001006058#10001' crapappid,10000.00 amt,TO_DATE('2011-08-01','yyyy-mm-dd') open_dt ,TO_DATE('2011-06-01','yyyy-mm-dd') pay_dt FROM dual UNION ALL
  3  SELECT '2' loanid,'R3300001006058#10002' crapappid,20000.00 amt,TO_DATE('2011-08-03','yyyy-mm-dd') open_dt ,TO_DATE('2011-05-12','yyyy-mm-dd') pay_dt FROM dual UNION ALL
  4  SELECT '3' loanid,'R3300001006058#10003' crapappid,50000.00 amt,TO_DATE('2011-07-02','yyyy-mm-dd') open_dt ,TO_DATE('2010-03-12','yyyy-mm-dd') pay_dt FROM dual UNION ALL
  5  SELECT '4' loanid,'R3300001003232#10001' crapappid,32000.00 amt,TO_DATE('2011-06-01','yyyy-mm-dd') open_dt ,TO_DATE('2011-03-12','yyyy-mm-dd') pay_dt FROM dual
  6  )
  7  SELECT m.* FROM (
  8    SELECT t.loanid,
  9           t.crapappid,
 10           t.amt,
 11           t.open_dt,
 12           SUM(CASE WHEN t.pay_dt > TO_DATE('2011-01-01','yyyy-mm-dd') THEN t.amt ELSE 0 END)
 13           OVER(PARTITION BY
 14                              CASE WHEN instr(t.crapappid,'#') > 0
 15                                   THEN substr(t.crapappid, 1, length(t.crapappid) - 5)
 16                                   ELSE t.crapappid
 17                              END
 18                              ) sumamt
 19      FROM bf_agt_lnp_ctrt_apply t) m
 20    WHERE m.open_dt > TO_DATE('2011-08-01','yyyy-mm-dd')
 21  ;LOANID CRAPAPPID                   AMT OPEN_DT         SUMAMT
------ -------------------- ---------- ----------- ----------
2      R3300001006058#10002      20000 2011/08/03       30000

Answer 8

搞定，select可以当做一个集合然后再select的。
谢谢hudingchen！