求一SQL语句!高手请进！！！

[一横一竖]

表结构
会员编号 交易日期 是否汇款
MemberID DealDate IsRemit

写一个SQL找出逾期未汇款的会员。
逾期未汇款定义：
1.根据系统当前日期，大于1个月已交易但没汇款的会员;
2.同一个会员连续两次且其中一次交易超过15天的会员，两次交易至少间隔一天，同一天内的两次交易视为一次交易;

[guishuanglin]

上面的正解.

[xiaoxiao1984]

demo@HASL>select * from a;

MEMBERID DEALDATE IS
---------- ------------------- --
1 2006-03-02 00:00:00 1
1 2006-09-18 00:00:00 0
2 2006-09-18 00:00:00 0
2 2006-09-22 00:00:00 0
3 2006-09-27 00:00:00 0
3 2006-09-27 00:00:00 0
4 2006-10-01 00:00:00 0
5 2006-09-01 00:00:00 0
1 2006-08-30 00:00:00 0
3 2006-09-27 00:00:00 1
3 2006-09-03 00:00:00 0
6 2006-09-25 00:00:00 0

已选择12行。

demo@HASL>select memberid, dealdate
2 from (
3 select memberid, dealdate
4 from a
5 where isremit = '0'
6 group by memberid, dealdate
7 ) TB
8 where months_between(sysdate,dealdate) >= 1
9 or exists (
10 select 1
11 from a TA
12 where TA.memberid = TB.memberid
13 and TA.isremit = '0'
14 and trunc(sysdate) - TA.DEALDATE >= 15
15 )
16 ;

MEMBERID DEALDATE
---------- -------------------
1 2006-08-30 00:00:00
1 2006-09-18 00:00:00
2 2006-09-18 00:00:00
2 2006-09-22 00:00:00
3 2006-09-03 00:00:00
3 2006-09-27 00:00:00
5 2006-09-01 00:00:00

已选择7行。

偷懒一下，*_*

[wangzk0206]

只是第一条规则很简单：
select * from test where sysdate-DealDate>30 and isremit='1'
第二条不知道什么意思 看不太明白

[一横一竖]

就是说两次交易但没有汇款的,并且都大于15天了也算是逾期未汇款会员。但若这两次是在同一天的就不算是逾期未汇款。只有相差一天的才能算。

[wangzk0206]

SQL> select * from test;

ID DEALDATE I
---------- ---------- -
1 2006-03-02 1
1 2006-09-18 0
2 2006-09-18 0
2 2006-09-19 0
3 2006-09-30 0
3 2006-09-30 0
4 2006-10-01 0
5 2006-09-01 0

已选择8行。

SQL> select id,dealdate,isresult from (
2 select id,dealdate,isresult,count(1)over(partition by id) rid,lag(dealdate)over(partition by id order by dealdate ) lag_date,
3 lead(dealdate)over(partition by id order by dealdate) lead_date from test
4 where isresult='0') a
5 where (a.rid=1 and sysdate-a.dealdate>30) or (sysdate-a.dealdate>15 and a.rid>1 and (a.lag_date<>a.dealdate or a.lead_date<>a.dealdate));

ID DEALDATE I
---------- ---------- -
2 2006-09-18 0
2 2006-09-19 0
5 2006-09-01 0

SQL>

[一横一竖]

select MemberID,count(*) from A where MemberID in(
select max(MemberID) from A b
where sysdate-b.DealDate>=1
group by MemberID,to_char(DealDate,'yyyymmdd'))
group by MemberID
having count(*)>1

这样不知道是不是可以取到呢？

[蓝琦]

to xiaoxiao1984(笨猫儿)
可以这样写吗
SQL> select memberid, dealdate
2 from (
3 select memberid, dealdate
4 from a
5 where isremit = '0'
6 group by memberid, dealdate
7 ) TB
8 where months_between(sysdate,dealdate) >= 1 or
9 trunc(sysdate) - TA.DEALDATE >= 15;

[xiaoxiao1984]

这样不可以呢,因为楼主要求的是
1.根据系统当前日期，大于1个月已交易但没汇款的会员;
2.同一个会员连续两次且其中一次交易超过15天的会员，两次交易至少间隔一天，同一天内的两次交易视为一次交易;

而楼上这么写的话,只要交易超过15天没有汇款的会员(包括仅交易一次) 都会被选择出来呢

[wangzk0206]

实现的方法应该很多 只要找到自己最适合的方法就可以了

[一横一竖]

加油啊