- 论坛徽章:
- 0
|
给你参考一下
create table CCS_ISSUE
(
ID VARCHAR2(10),
ISSUE VARCHAR2(10)
)
id issue
1 20080101 a
2 20080102 a
3 20080103 a
4 20080106 a
5 20080102 b
6 20080105 b
7 20080106 b
8 20080107 b
SELECT b.*
FROM
(SELECT ID,issue,1+decode(lead(ID,1,0) over(partition BY issue ORDER BY ID) ,to_char(to_date(id,'yyyymmdd')+1,'yyyymmdd'),2,0)+
decode(lead(ID,2,0) over(partition BY issue ORDER BY ID),to_char(to_date(id,'yyyymmdd')+2,'yyyymmdd'),3,0) total
FROM ccs_issue ) a, ccs_issue b
WHERE b.ID IN ( a.ID,to_char(to_date(a.id,'yyyymmdd')+1,'yyyymmdd'),to_char(to_date(a.id,'yyyymmdd')+2,'yyyymmdd'))
AND b.issue = a.issue
AND a.total = 6
id issue
1 20080101 a
2 20080102 a
3 20080103 a
4 20080105 b
5 20080106 b
6 20080107 b |
|