我现在又2个表之间的查询运算：表 a
ID guid code startdate
1 1e 123456 2010-10-1
2 2e 223546 2010-7-2
3 3e 132455 2008-5-4
4 4e 123324 2008-1-20
表 b
ID guid code startdate enddate
1 1e 123456 2009-1-10 2010-1-10
2 1e 123456 2010-2-1 2010-9-30
3 1e 123456 2008-1-10 2009-8-10
4 2e 223546 2009-7-10 2010-6-29
5 4e 123324 2007-5-10 2008-8-1
6 4e 123324 2006-1-1 2007-1-20
3 3e 132455 2008-5-4 查询要求是：
根据表a中的编号guid和code在表b中查找a.guid=b.guid and a.code=b.code,如果a表和b表中的数据时1对1的话呢则是new，如果是1对多的话（大于等于2）选择表b中b.enddate是最大的时间。并且判断
要求是a.startdate-b.enddate 小于等于5 则为Topup，大于5为Reloan。
要求的结果是表c：
[code]
ID guid code type
1 1e 123456 Topup
2 2e 223546 New
3 3e 132455 New
3 4e 123324 Reloan
[/code]
---------------
实际的表结果是a表有8000多条数据，b表有20万条数据（有重复的数据）；考虑下效率~！
问下大牛们有什么好的建议
ID guid code startdate
1 1e 123456 2010-10-1
2 2e 223546 2010-7-2
3 3e 132455 2008-5-4
4 4e 123324 2008-1-20
表 b
ID guid code startdate enddate
1 1e 123456 2009-1-10 2010-1-10
2 1e 123456 2010-2-1 2010-9-30
3 1e 123456 2008-1-10 2009-8-10
4 2e 223546 2009-7-10 2010-6-29
5 4e 123324 2007-5-10 2008-8-1
6 4e 123324 2006-1-1 2007-1-20
3 3e 132455 2008-5-4 查询要求是：
根据表a中的编号guid和code在表b中查找a.guid=b.guid and a.code=b.code,如果a表和b表中的数据时1对1的话呢则是new，如果是1对多的话（大于等于2）选择表b中b.enddate是最大的时间。并且判断
要求是a.startdate-b.enddate 小于等于5 则为Topup，大于5为Reloan。
要求的结果是表c：
[code]
ID guid code type
1 1e 123456 Topup
2 2e 223546 New
3 3e 132455 New
3 4e 123324 Reloan
[/code]
---------------
实际的表结果是a表有8000多条数据，b表有20万条数据（有重复的数据）；考虑下效率~！
问下大牛们有什么好的建议
1 1e 123456 Tope
2 2e 223546 New
3 3e 132455 New
4 4e 123324 Reloan
with a as(
select 1 ID,'1e' guid,123456 code,
to_date('2010-10-01','yyyy-mm-dd') startdate from dual union all
select 2,'2e',223546,to_date('2010-07-02','yyyy-mm-dd') from dual union all
select 3,'3e',132455,to_date('2008-05-04','yyyy-mm-dd') from dual union all
select 4,'4e',123324,to_date('2008-01-20','yyyy-mm-dd') from dual),
b as(
select 1 ID,'1e' guid,123456 code,
to_date('2009-01-10','yyyy-mm-dd') startdate,
to_date('2010-01-10','yyyy-mm-dd') enddate from dual union all
select 2,'1e',123456,to_date('2010-02-01','yyyy-mm-dd'),
to_date('2010-09-30','yyyy-mm-dd') from dual union all
select 3,'1e',123456,to_date('2008-01-10','yyyy-mm-dd'),
to_date('2009-08-10','yyyy-mm-dd') from dual union all
select 4,'2e',223546,to_date('2009-07-10','yyyy-mm-dd'),
to_date('2010-06-29','yyyy-mm-dd') from dual union all
select 5,'4e',123324,to_date('2007-05-10','yyyy-mm-dd'),
to_date('2008-08-01','yyyy-mm-dd') from dual union all
select 6,'4e',123324,to_date('2006-01-01','yyyy-mm-dd'),
to_date('2007-01-20','yyyy-mm-dd') from dual union all
select 7,'3e',132455,to_date('2008-05-04','yyyy-mm-dd'),null from dual)
--以上为提供数据的语句
select a.guid,a.code,max(a.startdate),max(b.enddate),
(case when max(b.enddate)-max(a.startdate)<=5 then 'Topup' else 'Reloan' end)
from b,a
where a.guid=b.guid and a.code=b.code
group by a.guid,a.code
GU CODE MAX(A.STARTDATE) MAX(B.ENDDATE) (CASEW
-- ---------- ------------------- ------------------- ------
2e 223546 2010-07-02 00:00:00 2010-06-29 00:00:00 Topup
1e 123456 2010-10-01 00:00:00 2010-09-30 00:00:00 Topup
3e 132455 2008-05-04 00:00:00 Reloan
4e 123324 2008-01-20 00:00:00 2008-08-01 00:00:00 Reloan
with a as(
select 1 ID,'1e' guid,123456 code,
to_date('2010-10-01','yyyy-mm-dd') startdate from dual union all
select 2,'2e',223546,to_date('2010-07-02','yyyy-mm-dd') from dual union all
select 3,'3e',132455,to_date('2008-05-04','yyyy-mm-dd') from dual union all
select 4,'4e',123324,to_date('2008-01-20','yyyy-mm-dd') from dual),
b as(
select 1 ID,'1e' guid,123456 code,
to_date('2009-01-10','yyyy-mm-dd') startdate,
to_date('2010-01-10','yyyy-mm-dd') enddate from dual union all
select 2,'1e',123456,to_date('2010-02-01','yyyy-mm-dd'),
to_date('2010-09-30','yyyy-mm-dd') from dual union all
select 3,'1e',123456,to_date('2008-01-10','yyyy-mm-dd'),
to_date('2009-08-10','yyyy-mm-dd') from dual union all
select 4,'2e',223546,to_date('2009-07-10','yyyy-mm-dd'),
to_date('2010-06-29','yyyy-mm-dd') from dual union all
select 5,'4e',123324,to_date('2007-05-10','yyyy-mm-dd'),
to_date('2008-08-01','yyyy-mm-dd') from dual union all
select 6,'4e',123324,to_date('2006-01-01','yyyy-mm-dd'),
to_date('2007-01-20','yyyy-mm-dd') from dual union all
select 7,'3e',132455,to_date('2008-05-04','yyyy-mm-dd'),null from dual)
select guid,code,
decode(rn,1,'new',
(case when enddate-startdate<=5 then 'Tope' else 'Reloan' end))
from (select a.guid,a.code,
max(a.startdate) startdate,
max(b.enddate) enddate,count(*) rn
from a,b
where a.guid=b.guid and a.code=b.code
group by a.guid,a.code)
order by guidGU CODE DECODE
-- ---------- ------
1e 123456 Tope
2e 223546 new
3e 132455 new
4e 123324 Reloan
2 select 1 ID,'1e' guid,123456 code,
3 to_date('2010-10-01','yyyy-mm-dd') startdate from dual union all
4 select 2,'2e',223546,to_date('2010-07-02','yyyy-mm-dd') from dual union all
5 select 3,'3e',132455,to_date('2008-05-04','yyyy-mm-dd') from dual union all
6 select 4,'4e',123324,to_date('2008-01-20','yyyy-mm-dd') from dual),
7 b as(
8 select 1 ID,'1e' guid,123456 code,
9 to_date('2009-01-10','yyyy-mm-dd') startdate,
10 to_date('2010-01-10','yyyy-mm-dd') enddate from dual union all
11 select 2,'1e',123456,to_date('2010-02-01','yyyy-mm-dd'),
12 to_date('2010-09-30','yyyy-mm-dd') from dual union all
13 select 3,'1e',123456,to_date('2008-01-10','yyyy-mm-dd'),
14 to_date('2009-08-10','yyyy-mm-dd') from dual union all
15 select 4,'2e',223546,to_date('2009-07-10','yyyy-mm-dd'),
16 to_date('2010-06-29','yyyy-mm-dd') from dual union all
17 select 5,'4e',123324,to_date('2007-05-10','yyyy-mm-dd'),
18 to_date('2008-08-01','yyyy-mm-dd') from dual union all
19 select 6,'4e',123324,to_date('2006-01-01','yyyy-mm-dd'),
20 to_date('2007-01-20','yyyy-mm-dd') from dual union all
21 select 7,'3e',132455,to_date('2008-05-04','yyyy-mm-dd'),null from dual)
22 --以上为提供数据的语句
23 select a.guid,a.code,max(a.startdate),max(b.enddate),
24 case when count(*)=1 then 'new' else(case when max(b.enddate)-max(a.startdate)<=5 then 'Topup' else 'Reloan' end) end
25 from b,a
26 where a.guid=b.guid and a.code=b.code
27 group by a.guid,a.code
28 ;
GUID CODE MAX(A.STARTDATE) MAX(B.ENDDATE) CASEWHENCOUNT(*)=1THEN'NEW'ELS
---- ---------- ---------------- -------------- ------------------------------
2e 223546 2010-7-2 2010-6-29 new
1e 123456 2010-10-1 2010-9-30 Topup
3e 132455 2008-5-4 new
4e 123324 2008-1-20 2008-8-1 Reloan
SQL>
SQL> with a as(
2 select 1 id,'1e' guid,123456 code, date '2010-10-1' startdate from dual union all
3 select 2 id,'2e' guid,223546 code, date '2010-7-2' startdate from dual union all
4 select 3 id,'3e' guid,132455 code, date '2008-5-4' startdate from dual union all
5 select 4 id,'4e' guid,123324 code, date '2008-1-20' startdate from dual
6 )
7 ,b as
8 (
9 select 1 id, '1e' guid,123456 code,date '2009-1-10' startdate,date '2010-1-10' enddate from dual union all
10 select 2 id, '1e' guid,123456 code,date '2010-2-1' startdate,date '2010-9-30' enddate from dual union all
11 select 3 id, '1e' guid,123456 code,date '2008-1-10' startdate,date '2009-8-10' enddate from dual union all
12 select 4 id, '2e' guid,223546 code,date '2009-7-10' startdate,date '2010-6-29' enddate from dual union all
13 select 5 id, '4e' guid,123324 code,date '2007-5-10' startdate,date '2008-8-1' enddate from dual union all
14 select 6 id, '4e' guid,123324 code,date '2006-1-1' startdate,date '2007-1-20' enddate from dual union all
15 select 3 id, '3e' guid,132455 code,date '2008-5-4' startdate,null enddate from dual
16 )
17 select a.id, a.guid, a.code, re
18 from (select a.id,
19 a.guid,
20 a.code,
21 case
22 when cn = 1 then
23 'New'
24 when cn > 1 and
25 (rn = 1 and extract(month from b.enddate) -
26 extract(month from b.startdate) <= 5) then
27 'TOPUP'
28 when cn > 1 and
29 (rn = 1 and extract(month from b.enddate) -
30 extract(month from b.startdate) > 5) then
31 'Reloan'
32 end re,
33 b.rn
34 from a,
35 (select b.*,
36 row_number() over(partition by guid, code order by enddate desc) rn,
37 count(*) over(partition by guid, code order by 1) cn
38 from b) b
39 where a.code = b.code
40 and a.guid = b.guid) a
41 where rn = 1
42 ; ID GUID CODE REMARK
---------- ---- ---------- ------
1 1e 123456 Reloan
2 2e 223546 New
3 3e 132455 New
4 4e 123324 TOPUPSQL>
-----修改下数据有点问题使用分析函数
SQL>
SQL> with a as(
2 select 1 id,'1e' guid,123456 code, date '2010-10-1' startdate from dual union all
3 select 2 id,'2e' guid,223546 code, date '2010-7-2' startdate from dual union all
4 select 3 id,'3e' guid,132455 code, date '2008-5-4' startdate from dual union all
5 select 4 id,'4e' guid,123324 code, date '2008-1-20' startdate from dual
6 )
7 ,b as
8 (
9 select 1 id, '1e' guid,123456 code,date '2009-1-10' startdate,date '2010-1-10' enddate from dual union all
10 select 2 id, '1e' guid,123456 code,date '2010-2-1' startdate,date '2010-9-30' enddate from dual union all
11 select 3 id, '1e' guid,123456 code,date '2008-1-10' startdate,date '2009-8-10' enddate from dual union all
12 select 4 id, '2e' guid,223546 code,date '2009-7-10' startdate,date '2010-6-29' enddate from dual union all
13 select 5 id, '4e' guid,123324 code,date '2007-5-10' startdate,date '2008-8-1' enddate from dual union all
14 select 6 id, '4e' guid,123324 code,date '2006-1-1' startdate,date '2007-1-20' enddate from dual union all
15 select 3 id, '3e' guid,132455 code,date '2008-5-4' startdate,null enddate from dual
16 )
17 select a.id, a.guid, a.code, re
18 from (select a.id,
19 a.guid,
20 a.code,
21 case
22 when cn = 1 then
23 'New'
24 when cn > 1 and (rn = 1 and b.enddate - a.startdate <= 5) then
25 'TOPUP'
26 when cn > 1 and (rn = 1 and b.enddate - a.startdate > 5) then
27 'Reloan'
28 end re,
29 b.rn
30 from a,
31 (select b.*,
32 row_number() over(partition by guid, code order by enddate desc) rn,
33 count(*) over(partition by guid, code order by 1) cn
34 from b) b
35 where a.code = b.code
36 and a.guid = b.guid) a
37 where rn = 1
38 ; ID GUID CODE REMARK
---------- ---- ---------- ------
1 1e 123456 TOPUP
2 2e 223546 New
3 3e 132455 New
4 4e 123324 ReloanSQL>
--楼上几位速度很快啊
with a as(
select 1 ID,'1e' guid,123456 code,to_date('2010-10-01','yyyy-mm-dd') startdate from dual
union all
select 2,'2e',223546,to_date('2010-07-02','yyyy-mm-dd') from dual
union all
select 3,'3e',132455,to_date('2008-05-04','yyyy-mm-dd') from dual
union all
select 4,'4e',123324,to_date('2008-01-20','yyyy-mm-dd') from dual
),
b as(
select 1 ID,'1e' guid,123456 code,to_date('2009-01-10','yyyy-mm-dd') startdate,to_date('2010-01-10','yyyy-mm-dd') enddate from dual
union all
select 2,'1e',123456,to_date('2010-02-01','yyyy-mm-dd'),to_date('2010-09-30','yyyy-mm-dd') from dual
union all
select 3,'1e',123456,to_date('2008-01-10','yyyy-mm-dd'),to_date('2009-08-10','yyyy-mm-dd') from dual
union all
select 4,'2e',223546,to_date('2009-07-10','yyyy-mm-dd'),to_date('2010-06-29','yyyy-mm-dd') from dual
union all
select 5,'4e',123324,to_date('2007-05-10','yyyy-mm-dd'),to_date('2008-08-01','yyyy-mm-dd') from dual
union all
select 6,'4e',123324,to_date('2006-01-01','yyyy-mm-dd'),to_date('2007-01-20','yyyy-mm-dd') from dual
union all
select 7,'3e',132455,to_date('2008-05-04','yyyy-mm-dd'),null from dual
)
--以上为提供数据的语句
select rownum id,a.* from(
select a.guid,a.code,
(case when Count(*)=1 then 'new'
when max(b.enddate)-max(a.startdate)<=5 then 'Topup'
else 'Reloan'
end) type
from b,a
where a.guid=b.guid and a.code=b.code
group by a.guid,a.code
) a
ID GUID CODE TYPE
-----------------------------
1 1e 123456 Topup
2 2e 223546 new
3 3e 132455 new
4 4e 123324 Reloan