/* 测试,以下理解为按bid连续 */create table 表a(aid int); insert into 表a values(1); insert into 表a values(2); insert into 表a values(3);create table 表b(bid int,aid int,zxzt char(1),cfrq date); insert into 表b values(1,1,'1',to_date('2005-01-21','yyyy-mm-dd')); insert into 表b values(2,1,'1',to_date('2005-01-02','yyyy-mm-dd')); insert into 表b values(3,1,'1',to_date('2005-01-03','yyyy-mm-dd')); insert into 表b values(4,1,'1',to_date('2005-01-11','yyyy-mm-dd'));insert into 表b values(6,2,'1',to_date('2005-01-01','yyyy-mm-dd')); insert into 表b values(7,2,'0',to_date('2005-01-02','yyyy-mm-dd')); insert into 表b values(8,2,'1',to_date('2005-01-03','yyyy-mm-dd')); insert into 表b values(9,3,'1',to_date('2004-01-01','yyyy-mm-dd')); insert into 表b values(9,3,'1',to_date('1999-01-21','yyyy-mm-dd')); select a.aid from 表a a,表b b where b.aid = a.aid and b.zxzt = '1' and trunc(b.cfrq+20)<trunc(sysdate) and exists(select 1 from 表b where aid = a.aid and zxzt = '1' and trunc(cfrq+20)<trunc(sysdate) and bid = (select min(bid) from 表b where bid>b.bid) );
/* AID ---------- 1已选择 1 行。 */drop table 表a; drop table 表b;
/* 上例中以bid理解连续,注意测试数据的最后两条特意弄成同一个bid,稍改一下测试数据后测试。 */ create table 表a(aid int); insert into 表a values(1); insert into 表a values(2); insert into 表a values(3);create table 表b(bid int,aid int,zxzt char(1),cfrq date); insert into 表b values(1,1,'1',to_date('2005-01-21','yyyy-mm-dd')); insert into 表b values(2,1,'1',to_date('2005-01-02','yyyy-mm-dd')); insert into 表b values(3,1,'1',to_date('2005-01-03','yyyy-mm-dd')); insert into 表b values(4,1,'1',to_date('2005-01-11','yyyy-mm-dd'));insert into 表b values(6,2,'1',to_date('2005-01-01','yyyy-mm-dd')); insert into 表b values(7,2,'0',to_date('2005-01-02','yyyy-mm-dd')); insert into 表b values(8,2,'1',to_date('2005-01-03','yyyy-mm-dd')); insert into 表b values(9,3,'1',to_date('2005-01-02','yyyy-mm-dd')); insert into 表b values(10,3,'1',to_date('2005-01-01','yyyy-mm-dd')); select distinct a.aid from 表a a,表b b where b.aid = a.aid and b.zxzt = '1' and trunc(b.cfrq+20)<trunc(sysdate) and exists(select 1 from 表b where aid = a.aid and zxzt = '1' and trunc(cfrq+20)<trunc(sysdate) and cfrq = (select min(cfrq) from 表b where cfrq>b.cfrq) ); /*结果为 AID ---------- 1 3 */select distinct a.aid from 表a a,表b b where b.aid = a.aid and b.zxzt = '1' and trunc(b.cfrq+20)<trunc(sysdate) and exists(select 1 from 表b where aid = a.aid and zxzt = '1' and trunc(cfrq+20)<trunc(sysdate) and bid = (select min(bid) from 表b where bid>b.bid) ); /*结果为 AID ---------- 1 3 */drop table 表a; drop table 表b;
--上面已有测试: create table 表a(aid int); insert into 表a values(1); insert into 表a values(2); insert into 表a values(3);create table 表b(bid int,aid int,zxzt char(1),cfrq date); insert into 表b values(1,1,'1',to_date('2005-01-21','yyyy-mm-dd')); insert into 表b values(2,1,'1',to_date('2005-01-02','yyyy-mm-dd')); insert into 表b values(3,1,'1',to_date('2005-01-03','yyyy-mm-dd')); insert into 表b values(4,1,'1',to_date('2005-01-11','yyyy-mm-dd'));insert into 表b values(6,2,'1',to_date('2005-01-01','yyyy-mm-dd')); insert into 表b values(7,2,'0',to_date('2005-01-02','yyyy-mm-dd')); insert into 表b values(8,2,'1',to_date('2005-01-03','yyyy-mm-dd')); insert into 表b values(9,3,'1',to_date('2005-01-02','yyyy-mm-dd')); insert into 表b values(10,3,'1',to_date('2005-01-01','yyyy-mm-dd')); select distinct a.aid from 表a a,表b b where b.aid = a.aid and b.zxzt = '1' and trunc(b.cfrq+20)<trunc(sysdate) and exists(select 1 from 表b where aid = a.aid and zxzt = '1' and trunc(cfrq+20)<trunc(sysdate) and cfrq = (select min(cfrq) from 表b where cfrq>b.cfrq) ); /*结果为 AID ---------- 1 3 */drop table 表a; drop table 表b;
To txlicenhe(马可): 我的跟你有点不一样,可能有点烦select distinct a.aid from a,b c where a.aid = c.aid and c.zxzt = '1' and trunc(c.cfrq+20)<trunc(sysdate) and exists(select 1 from b where b.aid = c.aid and b.zxzt = '1' and trunc(b.cfrq+20)<trunc(sysdate) and b.cfrq>c.cfrq ) and not exists(select 1 from b where b.aid = c.aid and b.zxzt = '0' and b.cfrq between c.cfrq and (select min(cfrq) from b where b.aid = c.aid and b.cfrq > c.cfrq) )
select a.name,count(*),min(b.cfrq),max(b.cfrq) from table_a a, (select aid,trunc(cfrq) cfrq,trunc(cfrq)-rank() over (partition by aid order by trunc(cfrq)) cc from table_b where zxzt=1) b, group by a.name,b.cc having count(*)>=20;
怎么会不行呢,看下面的测试数据,环境是oracle 8.1.7+windows2000 SQL> select * 2 from a; AID ---------- 1 2 3 4SQL> select * 2 from b; BID AID Z CFRQ ---------- ---------- - ---------- 1 1 1 21-1月 -05 2 1 1 02-1月 -05 3 1 1 03-1月 -05 4 1 1 11-1月 -05 5 1 1 12-1月 -05 6 2 1 01-1月 -05 7 2 0 02-1月 -05 8 2 1 03-1月 -05 9 3 1 02-1月 -05 10 3 1 01-1月 -05 11 4 1 01-1月 -05 BID AID Z CFRQ ---------- ---------- - ---------- 12 4 1 03-1月 -05 13 4 0 04-1月 -05 SQL> ed 已写入文件 afiedt.buf 1 select distinct a.aid 2 from a,b c 3 where a.aid = c.aid 4 and c.zxzt = '1' 5 and trunc(c.cfrq+20)<trunc(sysdate) 6 and exists(select 1 from b 7 where b.aid = c.aid and b.zxzt = '1' 8 and trunc(b.cfrq+20)<trunc(sysdate) 9 and b.cfrq>c.cfrq 10 ) 11 and not exists(select 1 from b 12 where b.aid = c.aid and b.zxzt = '0' 13 and b.cfrq between c.cfrq and 14 (select min(cfrq) from b 15 where b.aid = c.aid 16 and b.cfrq > c.cfrq) 17* )SQL> r 1 select distinct a.aid 2 from a,b c 3 where a.aid = c.aid 4 and c.zxzt = '1' 5 and trunc(c.cfrq+20)<trunc(sysdate) 6 and exists(select 1 from b 7 where b.aid = c.aid and b.zxzt = '1' 8 and trunc(b.cfrq+20)<trunc(sysdate) 9 and b.cfrq>c.cfrq 10 ) 11 and not exists(select 1 from b 12 where b.aid = c.aid and b.zxzt = '0' 13 and b.cfrq between c.cfrq and 14 (select min(cfrq) from b 15 where b.aid = c.aid 16 and b.cfrq > c.cfrq) 17* ) AID ---------- 1 3 4
不能象你这么理解,就是表B中有一条记录,它已经过了20天了还未缴款(cfrq+20>sysdate).
现就是要找到表B中至少有两条这样的记录(针对一个人),且这两条记录之间无该人的另外一条已缴款信息。
*/create table 表a(aid int);
insert into 表a values(1);
insert into 表a values(2);
insert into 表a values(3);create table 表b(bid int,aid int,zxzt char(1),cfrq date);
insert into 表b values(1,1,'1',to_date('2005-01-21','yyyy-mm-dd'));
insert into 表b values(2,1,'1',to_date('2005-01-02','yyyy-mm-dd'));
insert into 表b values(3,1,'1',to_date('2005-01-03','yyyy-mm-dd'));
insert into 表b values(4,1,'1',to_date('2005-01-11','yyyy-mm-dd'));insert into 表b values(6,2,'1',to_date('2005-01-01','yyyy-mm-dd'));
insert into 表b values(7,2,'0',to_date('2005-01-02','yyyy-mm-dd'));
insert into 表b values(8,2,'1',to_date('2005-01-03','yyyy-mm-dd')); insert into 表b values(9,3,'1',to_date('2004-01-01','yyyy-mm-dd'));
insert into 表b values(9,3,'1',to_date('1999-01-21','yyyy-mm-dd')); select a.aid from 表a a,表b b
where b.aid = a.aid and b.zxzt = '1'
and trunc(b.cfrq+20)<trunc(sysdate)
and exists(select 1 from 表b where aid = a.aid and zxzt = '1' and trunc(cfrq+20)<trunc(sysdate)
and bid = (select min(bid) from 表b where bid>b.bid) );
/*
AID
----------
1已选择 1 行。
*/drop table 表a;
drop table 表b;
上例中以bid理解连续,注意测试数据的最后两条特意弄成同一个bid,稍改一下测试数据后测试。
*/
create table 表a(aid int);
insert into 表a values(1);
insert into 表a values(2);
insert into 表a values(3);create table 表b(bid int,aid int,zxzt char(1),cfrq date);
insert into 表b values(1,1,'1',to_date('2005-01-21','yyyy-mm-dd'));
insert into 表b values(2,1,'1',to_date('2005-01-02','yyyy-mm-dd'));
insert into 表b values(3,1,'1',to_date('2005-01-03','yyyy-mm-dd'));
insert into 表b values(4,1,'1',to_date('2005-01-11','yyyy-mm-dd'));insert into 表b values(6,2,'1',to_date('2005-01-01','yyyy-mm-dd'));
insert into 表b values(7,2,'0',to_date('2005-01-02','yyyy-mm-dd'));
insert into 表b values(8,2,'1',to_date('2005-01-03','yyyy-mm-dd')); insert into 表b values(9,3,'1',to_date('2005-01-02','yyyy-mm-dd'));
insert into 表b values(10,3,'1',to_date('2005-01-01','yyyy-mm-dd')); select distinct a.aid from 表a a,表b b
where b.aid = a.aid and b.zxzt = '1'
and trunc(b.cfrq+20)<trunc(sysdate)
and exists(select 1 from 表b where aid = a.aid and zxzt = '1' and trunc(cfrq+20)<trunc(sysdate)
and cfrq = (select min(cfrq) from 表b where cfrq>b.cfrq) );
/*结果为
AID
----------
1
3
*/select distinct a.aid from 表a a,表b b
where b.aid = a.aid and b.zxzt = '1'
and trunc(b.cfrq+20)<trunc(sysdate)
and exists(select 1 from 表b where aid = a.aid and zxzt = '1' and trunc(cfrq+20)<trunc(sysdate)
and bid = (select min(bid) from 表b where bid>b.bid) );
/*结果为
AID
----------
1
3
*/drop table 表a;
drop table 表b;
是按cfrq连续的
不是这个意思。一个人1.1号一笔未缴款,1.2-一笔已缴款 1.3一笔未缴款
现在是1.27号,这个人有两笔未缴款但是中间有一笔未缴款,表示有两笔未缴款但是不连续一个人1.1号一笔未缴款,1.3一笔未缴款
现在是1.27号,两者之间无多余的已缴款记录,表示这个人有两笔未缴款且连续。
create table 表a(aid int);
insert into 表a values(1);
insert into 表a values(2);
insert into 表a values(3);create table 表b(bid int,aid int,zxzt char(1),cfrq date);
insert into 表b values(1,1,'1',to_date('2005-01-21','yyyy-mm-dd'));
insert into 表b values(2,1,'1',to_date('2005-01-02','yyyy-mm-dd'));
insert into 表b values(3,1,'1',to_date('2005-01-03','yyyy-mm-dd'));
insert into 表b values(4,1,'1',to_date('2005-01-11','yyyy-mm-dd'));insert into 表b values(6,2,'1',to_date('2005-01-01','yyyy-mm-dd'));
insert into 表b values(7,2,'0',to_date('2005-01-02','yyyy-mm-dd'));
insert into 表b values(8,2,'1',to_date('2005-01-03','yyyy-mm-dd')); insert into 表b values(9,3,'1',to_date('2005-01-02','yyyy-mm-dd'));
insert into 表b values(10,3,'1',to_date('2005-01-01','yyyy-mm-dd')); select distinct a.aid from 表a a,表b b
where b.aid = a.aid and b.zxzt = '1'
and trunc(b.cfrq+20)<trunc(sysdate)
and exists(select 1 from 表b where aid = a.aid and zxzt = '1' and trunc(cfrq+20)<trunc(sysdate)
and cfrq = (select min(cfrq) from 表b where cfrq>b.cfrq) );
/*结果为
AID
----------
1
3
*/drop table 表a;
drop table 表b;
我的跟你有点不一样,可能有点烦select distinct a.aid
from a,b c
where a.aid = c.aid
and c.zxzt = '1'
and trunc(c.cfrq+20)<trunc(sysdate)
and exists(select 1 from b
where b.aid = c.aid and b.zxzt = '1'
and trunc(b.cfrq+20)<trunc(sysdate)
and b.cfrq>c.cfrq
)
and not exists(select 1 from b
where b.aid = c.aid and b.zxzt = '0'
and b.cfrq between c.cfrq and
(select min(cfrq) from b
where b.aid = c.aid
and b.cfrq > c.cfrq)
)
而且很难理解懂,有没有大侠写成存储过程那样来取得结果的呢?
from table_a a,
(select aid,trunc(cfrq) cfrq,trunc(cfrq)-rank() over (partition by aid order by trunc(cfrq)) cc from table_b where zxzt=1) b,
group by a.name,b.cc
having count(*)>=20;
SQL> select *
2 from a; AID
----------
1
2
3
4SQL> select *
2 from b; BID AID Z CFRQ
---------- ---------- - ----------
1 1 1 21-1月 -05
2 1 1 02-1月 -05
3 1 1 03-1月 -05
4 1 1 11-1月 -05
5 1 1 12-1月 -05
6 2 1 01-1月 -05
7 2 0 02-1月 -05
8 2 1 03-1月 -05
9 3 1 02-1月 -05
10 3 1 01-1月 -05
11 4 1 01-1月 -05 BID AID Z CFRQ
---------- ---------- - ----------
12 4 1 03-1月 -05
13 4 0 04-1月 -05
SQL> ed
已写入文件 afiedt.buf 1 select distinct a.aid
2 from a,b c
3 where a.aid = c.aid
4 and c.zxzt = '1'
5 and trunc(c.cfrq+20)<trunc(sysdate)
6 and exists(select 1 from b
7 where b.aid = c.aid and b.zxzt = '1'
8 and trunc(b.cfrq+20)<trunc(sysdate)
9 and b.cfrq>c.cfrq
10 )
11 and not exists(select 1 from b
12 where b.aid = c.aid and b.zxzt = '0'
13 and b.cfrq between c.cfrq and
14 (select min(cfrq) from b
15 where b.aid = c.aid
16 and b.cfrq > c.cfrq)
17* )SQL> r
1 select distinct a.aid
2 from a,b c
3 where a.aid = c.aid
4 and c.zxzt = '1'
5 and trunc(c.cfrq+20)<trunc(sysdate)
6 and exists(select 1 from b
7 where b.aid = c.aid and b.zxzt = '1'
8 and trunc(b.cfrq+20)<trunc(sysdate)
9 and b.cfrq>c.cfrq
10 )
11 and not exists(select 1 from b
12 where b.aid = c.aid and b.zxzt = '0'
13 and b.cfrq between c.cfrq and
14 (select min(cfrq) from b
15 where b.aid = c.aid
16 and b.cfrq > c.cfrq)
17* ) AID
----------
1
3
4