需求;连续3个月未申报(GL_WSBXX表,SBBZ申报标志,Y为已申报,N为未申报)。申报时间的列是SBRQ
即要查询GL_WSBXX表中SBRQ连续3个月SBBZ为N的信息。
即要查询GL_WSBXX表中SBRQ连续3个月SBBZ为N的信息。
解决方案 »
- oracle:在触发器中,如何获得自增id的值,并实现更新数据!
- 升级timesten11小版本需要打哪些补丁
- ORACLE客户端 ORA-12541 TNS 没有监听 问题 !请帮忙
- oracle中如何查找long raw的内容?
- 这个sqlserver的存储过程如何改成ORACLE的???
- 两个数据库(Oracle9和SQL Server2000)
- 我拿到一个oracle的备份文件,要恢复他。里面都是*.dmp文件。他们是oracle的文件吗?怎么恢复啊?
- 300分!! 求解oracle8i中的long类型怎么才能实现用like进行模糊查询?急!!
- 网线断掉后超时问题
- ORA-01839: 指定月份的日期无效
- 菜鸟问题
- 用法
create table GL_WSBXX(id number,sbrq date,sbbz varchar2(10));--插入记录
insert into gl_wsbxx (ID, SBRQ, SBBZ)
values (1, to_date('01-01-2009', 'dd-mm-yyyy'), 'N');insert into gl_wsbxx (ID, SBRQ, SBBZ)
values (1, to_date('01-02-2009', 'dd-mm-yyyy'), 'N');insert into gl_wsbxx (ID, SBRQ, SBBZ)
values (1, to_date('01-03-2009', 'dd-mm-yyyy'), 'N');insert into gl_wsbxx (ID, SBRQ, SBBZ)
values (2, to_date('01-01-2009', 'dd-mm-yyyy'), 'N');insert into gl_wsbxx (ID, SBRQ, SBBZ)
values (2, to_date('01-02-2009', 'dd-mm-yyyy'), 'Y');insert into gl_wsbxx (ID, SBRQ, SBBZ)
values (2, to_date('01-03-2009', 'dd-mm-yyyy'), 'N');insert into gl_wsbxx (ID, SBRQ, SBBZ)
values (2, to_date('01-04-2009', 'dd-mm-yyyy'), 'N');--取得连续三个月单位id
SELECT A.ID
FROM (SELECT * FROM GL_WSBXX) a,
(SELECT id, SBRQ, SBBZ, last_day(trunc(add_months(SBRQ, 2))) SBRQ1 FROM GL_WSBXX) b
WHERE a.id = b.id AND
a.SBRQ >= b.SBRQ AND
a.SBRQ <= b.SBRQ1
and a.sbbz = 'N'
and b.sbbz = 'N'
GROUP BY a.id,b.SBRQ1
HAVING COUNT(1) >= 3;
Connected as csdn
SQL>
SQL> --建表(其中id为单位ID)
SQL> drop table GL_WSBXX;Table droppedSQL> create table GL_WSBXX(id number, sbrq date, sbbz varchar2(10));Table createdSQL> --插入记录
SQL> insert into gl_wsbxx
2 (ID, SBRQ, SBBZ)
3 values
4 (1, to_date('01-01-2009', 'dd-mm-yyyy'), 'N');1 row insertedSQL> insert into gl_wsbxx
2 (ID, SBRQ, SBBZ)
3 values
4 (1, to_date('02-02-2009', 'dd-mm-yyyy'), 'N');1 row insertedSQL> insert into gl_wsbxx
2 (ID, SBRQ, SBBZ)
3 values
4 (1, to_date('03-03-2009', 'dd-mm-yyyy'), 'N');1 row insertedSQL> insert into gl_wsbxx
2 (ID, SBRQ, SBBZ)
3 values
4 (2, to_date('01-01-2009', 'dd-mm-yyyy'), 'N');1 row insertedSQL> insert into gl_wsbxx
2 (ID, SBRQ, SBBZ)
3 values
4 (2, to_date('01-02-2009', 'dd-mm-yyyy'), 'Y');1 row insertedSQL> insert into gl_wsbxx
2 (ID, SBRQ, SBBZ)
3 values
4 (2, to_date('01-03-2009', 'dd-mm-yyyy'), 'N');1 row insertedSQL> insert into gl_wsbxx
2 (ID, SBRQ, SBBZ)
3 values
4 (2, to_date('01-04-2009', 'dd-mm-yyyy'), 'N');1 row insertedSQL> commit;Commit completeSQL> select id
2 from (select id,
3 sbrq,
4 sbbz,
5 add_months(trunc(sbrq, 'mm'),
6 1 - rank() over(partition by id order by sbrq)) ord
7 from (select id, sbrq, sbbz from gl_wsbxx where sbbz = 'N'))
8 group by id, ord
9 having count(*) >= 3
10 / ID
----------
1SQL>