求高手帮忙写个有难度的SQL

Question

CREATE TABLE test1
(
  CID             INTEGER                       NOT NULL,
  MAID             INTEGER                       NOT NULL,
  CALLINGNUM       VARCHAR2(20 BYTE)             NOT NULL, --呼叫号码
)insert into test1 values(1,1,'15800000000');
insert into test1 values(2,2,'15812345678');
insert into test1 values(3,3,'0075000');
insert into test1 values(3,4,'10086');CREATE TABLE test2
(
  CID           INTEGER                         NOT NULL
)insert into test2 values(1);
insert into test2 values(2);
insert into test2 values(3);
insert into test2 values(4);CREATE TABLE test3
(
  MAID          INTEGER                         NOT NULL,
  PRICES        NUMBER(5,2)                     NOT NULL--套餐费用
)insert into test3 values(1,10);
insert into test3 values(2,20);
insert into test3 values(3,30);
insert into test3 values(4,40);
CREATE TABLE test4
(
  CALLINGNUM  VARCHAR2(20 BYTE)                 NOT NULL,--呼叫号码
  CONCOST     NUMBER                            NOT NULL--通话费用
)insert into test4 values('15800000000',20);
insert into test4 values('15812345678',10);
insert into test4 values('0075000',30);
insert into test4 values('10086',40);

Answer 1

重新发下，请大家帮忙看下：
CREATE TABLE test1
(
  CID             INTEGER                       NOT NULL,
  MAID             INTEGER                       NOT NULL,
  CALLINGNUM       VARCHAR2(20 BYTE)             NOT NULL, --呼叫号码
)insert into test1 values(1,1,'15800000000');
insert into test1 values(2,2,'15812345678');
insert into test1 values(3,3,'0075000');
insert into test1 values(3,4,'10086');CREATE TABLE test2
(
  CID           INTEGER                         NOT NULL
)insert into test2 values(1);
insert into test2 values(2);
insert into test2 values(3);
insert into test2 values(4);CREATE TABLE test3
(
  MAID          INTEGER                         NOT NULL,
  PRICES        NUMBER(5,2)                     NOT NULL--套餐费用
)insert into test3 values(1,10);
insert into test3 values(2,20);
insert into test3 values(3,30);
insert into test3 values(4,40);
CREATE TABLE test4
(
  CALLINGNUM  VARCHAR2(20 BYTE)                 NOT NULL,--呼叫号码
  CONCOST     NUMBER                            NOT NULL--通话费用
)insert into test4 values('15800000000',1);
insert into test4 values('15800000000',2);
insert into test4 values('15812345678',15);
insert into test4 values('15812345678',10);
insert into test4 values('0075000',30);
insert into test4 values('0075000',30);
insert into test4 values('10086',40);
insert into test4 values('10086',40);当呼叫号码以00或10086开头时，直接读取CONCOST；当呼叫号码不是以00或10086开头时，且通话费用大于套餐费用时读取concost的总和，当通话费用小于套餐费用时直接读取套餐费用，最后将3类费用相加。
需要的结果
callednum      concost
15800000000      10
15812345678      25
00750000         60
10086            80

Answer 2

select t1.cid,
       t1.callingnum,
       case
         when t1.callingnum like '00%' or t1.callingnum like '10086%' then
          sum(t4.concost)
         else
          greatest(sum(t4.concost), t3.prices)
       end
  from test1 t1, test3 t3, test4 t4
 where t1.maid = t3.maid(+)
   and t1.callingnum = t4.callingnum
 group by t1.cid, t1.CALLINGNUM, t3.prices

Answer 3

答案有点不同，我的SQL算出来15800000000的concost为3select callingnum, sum(cost)
  from (select t5.*,
               case
                 when t5.r = 1 then
                  t5.concost
                 when t5.r = 1 and concost < prices then
                  t5.prices
                 else
                  t5.concost
               end cost
          from (select t4.callingnum,
                       prices,
                       concost,
                       decode(substr(t4.callingnum, 1, 2),
                              '00',
                              1,
                              decode(substr(t4.callingnum, 1, 5),
                                     '10086',
                                     1,
                                     0)) r
                  from test1 t1, test2 t2, test3 t3, test4 t4
                 where t1.cid = t2.cid
                   and t3.maid = t1.maid
                   and t4.callingnum = t1.callingnum) t5)
 group by callingnum;

Answer 4

答案有点不同，我的SQL算出来15800000000的concost为3select callingnum, sum(cost)
  from (select t5.*,
               case
                 when t5.r = 1 then
                  t5.concost
                 when t5.r = 1 and concost < prices then
                  t5.prices
                 else
                  t5.concost
               end cost
          from (select t4.callingnum,
                       prices,
                       concost,
                       decode(substr(t4.callingnum, 1, 2),
                              '00',
                              1,
                              decode(substr(t4.callingnum, 1, 5),
                                     '10086',
                                     1,
                                     0)) r
                  from test1 t1, test2 t2, test3 t3, test4 t4
                 where t1.cid = t2.cid
                   and t3.maid = t1.maid
                   and t4.callingnum = t1.callingnum) t5)
 group by callingnum;3小于套餐费用10，应该读取10哦

Answer 5

答案有点不同，我的SQL算出来15800000000的concost为3select callingnum, sum(cost)
  from (select t5.*,
               case
                 when t5.r = 1 then
                  t5.concost
                 when t5.r = 1 and concost < prices then
                  t5.prices
                 else
                  t5.concost
               end cost
          from (select t4.callingnum,
                       prices,
                       concost,
                       decode(substr(t4.callingnum, 1, 2),
                              '00',
                              1,
                              decode(substr(t4.callingnum, 1, 5),
                                     '10086',
                                     1,
                                     0)) r
                  from test1 t1, test2 t2, test3 t3, test4 t4
                 where t1.cid = t2.cid
                   and t3.maid = t1.maid
                   and t4.callingnum = t1.callingnum) t5)
 group by callingnum;3小于套餐费用10，应该读取10哦
理解都错了，更正如下：select t5.callingnum,
       case
         when t5.r = 1 then
          t5.concost
         when t5.r = 0 and concost < prices then
          t5.prices
         else
          t5.concost
       end cost
  from (select t4.callingnum,
               prices,
               concost,
               decode(substr(t4.callingnum, 1, 2),
                      '00',
                      1,
                      decode(substr(t4.callingnum, 1, 5), '10086', 1, 0)) r
          from test1 t1,
               test2 t2,
               test3 t3,
               (select callingnum, sum(concost) concost
                  from test4
                 group by callingnum) t4
         where t1.cid = t2.cid
           and t3.maid = t1.maid
           and t4.callingnum = t1.callingnum) t5