求助SQL语句(分组按排序更新删除数据)

显示全部楼层 · 2009-10-14 18:48:55

表TEST:
id 主键
loanid 为贷款号
status 状态值
orderdate 生成日期
other 其他等值
sql:
create table test1(id integer,loanid integer,status integer,orderdate date,other integer);
insert into test1 values(1,1011,1,to_date('20090201','yyyymmdd'),3);
insert into test1 values(2,1011,2,to_date('20090201','yyyymmdd'),1);
insert into test1 values(3,1011,2,to_date('20090203','yyyymmdd'),311);
insert into test1 values(4,1011,1,to_date('20090204','yyyymmdd'),123);
insert into test1 values(5,1011,3,to_date('20080201','yyyymmdd'),33);
insert into test1 values(6,1012,1,to_date('20080301','yyyymmdd'),775);
insert into test1 values(7,1012,1,to_date('20080401','yyyymmdd'),55);
insert into test1 values(8,1012,1,to_date('20080101','yyyymmdd'),31);
insert into test1 values(9,1013,1,to_date('20090801','yyyymmdd'),883);
insert into test1 values(10,1013,2,to_date('20090801','yyyymmdd'),12);
insert into test1 values(11,1013,1,to_date('20090601','yyyymmdd'),23);
insert into test1 values(12,1013,3,to_date('20090701','yyyymmdd'),45);
insert into test1 values(13,1013,3,to_date('20090401','yyyymmdd'),1);
insert into test1 values(14,1013,1,to_date('20090801','yyyymmdd'),354);
commit;
实现功能：
根据loanid分组，根据orderdate排序，如果前后两个status值相同，则把最后排序的orderdate值更新到最前的orderdate，
并删除后面的记录。other值取最小排序的数值。
即：在相同loanid下，在连续的时间段下，只能保留一个status值，且在连续时间段下的最小的orderdate值更新为最大的orderdate值。
例如：
我用查询语句：
select * from test1
order by loanid,orderdate;
ID
LOANID
STATUS
ORDERDATE
OTHER
5
1011
3
2008-2-1
33
1
1011
1
2009-2-1
3
2
1011
2
2009-2-1
1
3
1011
2
2009-2-3
311
4
1011
1
2009-2-4
123
8
1012
1
2008-1-1
31
6
1012
1
2008-3-1
775
7
1012
1
2008-4-1
55
13
1013
3
2009-4-1
1
11
1013
1
2009-6-1
23
12
1013
3
2009-7-1
45
9
1013
1
2009-8-1
883
10
1013
2
2009-8-1
12
14
1013
1
2009-8-1
354
得到的结果应该为：
5
1011
3
2008-2-1
33
1
1011
1
2009-2-1
3
2
1011
2
2009-2-3
1
4
1011
1
2009-2-4
123
8
1012
1
2008-4-1
31
13
1013
3
2009-4-1
1
11
1013
1
2009-6-1
23
12
1013
3
2009-7-1
45
9
1013
1
2009-8-1
883
10
1013
2
2009-8-1
12
14
1013
1
2009-8-1
354
[ 本帖最后由 kingtmx 于 2010-2-9 12:16 编辑 ]

千问 · 2009-10-14 18:48:55

delete from test1 a where
exists(select 1 from test1 b where a.loanid=b.loanid and a.status=b.status and a.orderdate 本帖最后由 gzboy007 于 2010-2-9 12:07 编辑 ]

千问 · 2009-10-14 18:48:55

结果应该为：
5
1011
3
2008-2-1
33
1
1011
1
2009-2-1
3
2
1011
2
2009-2-3
1
4
1011
1
2009-2-4
123
8
1012
1
2008-4-1
31
13
1013
3
2009-4-1
1
11
1013
1
2009-6-1
23
12
1013
3
2009-7-1
45
9
1013
1
2009-8-1
883
10
1013
2
2009-8-1
12
14
1013
1
2009-8-1
354

我的意思是：在同一个loanid下，根据orderdate排序，在连续的时间段内，如果相邻的两条记录如果status值相同，那么就需要
将后一条记录的orderdate更新到前一条记录的orderdate,并且删除后1条记录。
比如：
IDLOANIDSTATUSORDERDATEOTHER
1 1011
1
20080101 12
2 1011
2
20080102 1
3 1011
2
20080103 4
4
1011
2
20080104 5
5
1011
4
20080105 2
6
1011
4
20080106
7
结果：
IDLOANIDSTATUSORDERDATEOTHER
1 1011
1
20080101 12
2 1011
2
20080104 1
5
1011
4
20080106
2
[ 本帖最后由 kingtmx 于 2010-2-9 12:09 编辑 ]

千问 · 2009-10-14 18:48:55

很抱歉，我没有说清楚。
我重新更新了帖子。

千问 · 2009-10-14 18:48:55

原帖由 kingtmx 于 2010-2-9 09:37 发表
表TEST:
id 主键
loanid 为贷款号
status 状态值
orderdate 生成日期
other 其他等值

实现功能：
根据loanid分组，根据orderdate排序，如果前后两个status值相同，则把最后排序的orderdate值更新到最前的orderdate，
并删除后面的记录。other值取最小排序的数值。
即：在相同loanid下，在连续的时间段下，只能保留一个status值，且在连续时间段下的最小的orderdate值更新为最大的orderdate值。
例如：
我用查询语句：
select * from test1
order by loanid,orderdate;
ID
LOANID
STATUS
ORDERDATE
OTHER
5
1011
3
2008-2-1
33
1
1011
1
2009-2-1
3
2
1011
2
2009-2-1
1
3
1011
2
2009-2-3
311
4
1011
1
2009-2-4
123
8
1012
1
2008-1-1
31
6
1012
1
2008-3-1
775
7
1012
1
2008-4-1
55
13
1013
3
2009-4-1
1
11
1013
1
2009-6-1
23
12
1013
3
2009-7-1
45
9
1013
1
2009-8-1
883
10
1013
2
2009-8-1
12
14
1013
1
2009-8-1
354
得到的结果应该为：
5
1011
3
2008-2-1
33
1
1011
1
2009-2-1
3
2
1011
2
2009-2-3
1
4
1011
1
2009-2-4
123
8
1012
1
2008-4-1
31
13
1013
3
2009-4-1
1
11
1013
1
2009-6-1
23
12
1013
3
2009-7-1
45
9
1013
1
2009-8-1
883
10
1013
2
2009-8-1
12
14
1013
1
2009-8-1
354

如果是这样的结果，那列ORDERDATE是否是连续的时间就没有任何关系了，只是列STATUS相邻的值相同进行处理即可！
如果是这样的话，那如下的语句可以满足你的要求！（如果数据量大的话，你可以用CATS提高性能）
具体如下：
select id,
loanid,
status,
orderdate,
other
from
(selectid,
loanid,
status,
max(orderdate) over(partition by loanid, rn) orderdate,
min(other) over(partition by loanid, rn) other,
row_number() over(partition by loanid, rn order by orderdate) flag,
from
(selectid,
loanid,
status,
orderdate,
other,
max(rn) over(partition by loanid order by orderdate) rn
from
(select id,
loanid,
status,
orderdate,
other,
decode(status,

lag(status) over(partition by loanid order by orderdate),

0,

row_number() over(partition by loanid order by orderdate)) rn
from test1)))
where flag = 1;

[ 本帖最后由 bell6248 于 2010-2-9 12:45 编辑 ]

千问 · 2009-10-14 18:48:55

各位有什么实现的方法吗？

千问 · 2009-10-14 18:48:55

原帖由 kingtmx 于 2010-2-9 12:45 发表
各位有什么实现的方法吗？
我在#8楼已经提供了方法，你测试一下，如果不满足，再改！