select * from my_view order by 变更日期;
姓名 个人身份 变更日期 免缴类别
-------------------- -------- ----------- --------
朱雪霞 75 2008-8-12 4
朱雪霞 75 2008-10-1
朱雪霞 75 2009-5-21
朱雪霞 75 2009-8-28 4
朱雪霞 75 2009-9-1
数据如上所示,这是一个存储变更信息的表,问题是这样的,如果上条记录的免缴类别不为空,下面有某条记录的免缴类别为空,就要这样显示
开始时间 结束时间 免缴类别
2008-8-12 2008-10-1 4
如果相邻几条记录的免缴类别一样则不要管
上面数据的最终显示效果要是下面这样:
开始时间 结束时间 免缴类别
2008-8-12 2008-10-1 4
2009-8-28 2009-9-1 4
不问题描述清楚了没有,请高手指点
姓名 个人身份 变更日期 免缴类别
-------------------- -------- ----------- --------
朱雪霞 75 2008-8-12 4
朱雪霞 75 2008-10-1
朱雪霞 75 2009-5-21
朱雪霞 75 2009-8-28 4
朱雪霞 75 2009-9-1
数据如上所示,这是一个存储变更信息的表,问题是这样的,如果上条记录的免缴类别不为空,下面有某条记录的免缴类别为空,就要这样显示
开始时间 结束时间 免缴类别
2008-8-12 2008-10-1 4
如果相邻几条记录的免缴类别一样则不要管
上面数据的最终显示效果要是下面这样:
开始时间 结束时间 免缴类别
2008-8-12 2008-10-1 4
2009-8-28 2009-9-1 4
不问题描述清楚了没有,请高手指点
解决方案 »
- oracle建立定时计划报错:ORA-06550第 7 行, 第 88 列: PLS-00103: 出现符号 "FMWW"在需要下列之一时:
- linux下安装oracle10g
- 为什么存储过程没错,就是一运行就报Exception啊?
- 字符集的疑惑???
- listener.ora中的参数的问题?
- 求一sql语句
- 请问,在oracle 怎么写sql语句查询dwm数据库rep用户以t开头的表啊,谢谢,急
- 创建repository时出现的问题!help!
- varchar2 与 varchar(10)的区别 ?
- ??用exp导出表时,出现如下问题(用sqlplus也是),tns可以正常启动。请帮助!
- myeclipse6.0 无法连接oracle
- 查询求高人
select '朱雪霞' name, 75 id, date'2008-8-12' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2008-10-1' changedate, null type from dual union all
select '朱雪霞' name, 75 id, date'2009-5-21' changedate, null type from dual union all
select '朱雪霞' name, 75 id, date'2009-8-28' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-9-1' changedate, null type from dual)
SELECT prev_date "开始时间", changedate "结束时间", prev_type "免缴类别"
FROM (SELECT t.*,
lag(t.changedate) over(PARTITION BY NAME, id ORDER BY changedate) prev_date,
lag(t.type) over(PARTITION BY NAME, id ORDER BY changedate) prev_type
FROM test t)
WHERE prev_type IS NOT NULL;
SQL> desc my_view
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
NAME VARCHAR2(10) Y
ID INTEGER Y
C_DATE DATE Y
TYPE INTEGER Y
SQL> select * from my_view;
NAME ID C_DATE TYPE
---------- --------------------------------------- ----------- ---------------------------------------
朱雪霞 75 2008-08-12 4
朱雪霞 75 2008-10-01
朱雪霞 75 2009-05-21
朱雪霞 75 2009-08-28 4
朱雪霞 75 2009-09-01
SQL>
SQL> SELECT *
2 FROM (SELECT NAME,
3 ID,
4 C_DATE 开始时间,
5 LEAD(C_DATE) OVER(PARTITION BY ID ORDER BY C_DATE) 结束时间,
6 TYPE
7 FROM MY_VIEW)
8 WHERE TYPE IS NOT NULL;
NAME ID 开始时间 结束时间 TYPE
---------- --------------------------------------- ----------- ----------- ---------------------------------------
朱雪霞 75 2008-08-12 2008-10-01 4
朱雪霞 75 2009-08-28 2009-09-01 4
select '朱雪霞' name, 75 id, date'2008-8-12' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2008-10-1' changedate, null type from dual union all
select '朱雪霞' name, 75 id, date'2009-5-21' changedate, null type from dual union all
select '朱雪霞' name, 75 id, date'2009-8-28' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-9-1' changedate, null type from dual)
SELECT prev_date "开始时间", changedate "结束时间", prev_type "免缴类别"
FROM (SELECT t.*,
lag(t.changedate) over(PARTITION BY NAME, id ORDER BY changedate) prev_date,
lag(t.type) over(PARTITION BY NAME, id ORDER BY changedate) prev_type
FROM test t)
WHERE prev_type IS NOT NULL;
select '朱雪霞' name, 75 id, date'2008-8-12' changedate, 3 type from dual union all
select '朱雪霞' name, 75 id, date'2008-10-1' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-5-21' changedate, null type from dual union all
select '朱雪霞' name, 75 id, date'2009-8-28' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-9-1' changedate, null type from dual)
select * from (select test.*,lead(type) over(order by changedate)cd from test) t
where t.type is not null and t.cd is null
select '朱雪霞' name, 75 id, date'2008-8-12' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2008-10-1' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-5-21' changedate, null type from dual union all
select '朱雪霞' name, 75 id, date'2009-8-28' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-9-1' changedate, null type from dual)
SELECT prev_date "开始时间", changedate "结束时间", prev_type "免缴类别"
FROM (
select name,id,prev_type,changedate,lag(changedate) over(PARTITION BY NAME, id ORDER BY name) prev_date ,rownum rn from (
SELECT t.*,lag(t.type) over(PARTITION BY NAME, id ORDER BY changedate) prev_type
FROM test t
)where type is null or prev_type is null
)
WHERE mod(rn,2)=0 ;
试试
select '朱雪霞' name, 75 id, date'2008-8-12' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2008-10-1' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-5-21' changedate, null type from dual union all
select '朱雪霞' name, 75 id, date'2009-8-28' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-9-1' changedate, null type from dual)
select * from (select test.*,lead(type) over(order by changedate)cd from test) t
where t.type is not null and t.cd is null
select '朱雪霞' name, 75 id, date'2008-8-12' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2008-10-1' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-5-21' changedate, null type from dual union all
select '朱雪霞' name, 75 id, date'2009-8-28' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-9-1' changedate, null type from dual)
select * from (select test.*,lag(type) over(order by changedate)cd from test) t
where t.type is null and t.cd is not null
select '朱雪霞' name, 75 id, date'2008-8-12' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2008-10-1' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-5-21' changedate, null type from dual union all
select '朱雪霞' name, 75 id, date'2009-8-28' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-9-1' changedate, null type from dual)
select t1.changedate "开始时间", t2.changedate "结束时间", t1.type "免缴类别"
from ((select tt.*,row_number() over (order by 1) rn from
(select * from (select test.*,lag(type) over (order by changedate)type1 from test )
where type is not null and type1 is null)tt) ) t1,
((select tt.*,row_number() over (order by 1) rn from
(select * from (select test.*,lag(type) over (order by changedate)type1 from test )
where type is null and type1 is not null)tt) )t2 where t1.rn=t2.rn
with test as(
select '朱雪霞' name, 75 id, date'2008-8-12' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2008-10-1' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-5-21' changedate, null type from dual union all
select '朱雪霞' name, 75 id, date'2009-8-28' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-9-1' changedate, null type from dual)
SELECT MIN(changedate) "开始时间", MAX(changedate) "结束时间", MAX(TYPE) "免缴类别"
FROM (SELECT t.*,
lag(t.changedate) over(PARTITION BY NAME, id ORDER BY changedate) prev_date,
lag(t.type) over(PARTITION BY NAME, id ORDER BY changedate) prev_type
FROM test t)
WHERE TYPE IS NOT NULL OR
prev_type IS NOT NULL
START WITH TYPE IS NOT NULL AND
prev_type IS NULL
CONNECT BY PRIOR changedate = prev_date AND
prev_type IS NOT NULL
GROUP BY NAME, id, rownum - LEVEL
ORDER BY MIN(changedate)
select '朱雪霞' name, 75 id, date'2008-8-12' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2008-10-1' changedate, null type from dual union all
select '朱雪霞' name, 75 id, date'2009-5-21' changedate, null type from dual union all
select '朱雪霞' name, 75 id, date'2009-8-28' changedate, 4 type from dual union all
select '朱雪霞' name, 75 id, date'2009-9-1' changedate, null type from dual)
SELECT prev_date "开始时间", changedate "结束时间", prev_type "免缴类别"
FROM (SELECT t.*,
lag(t.changedate) over(PARTITION BY NAME, id ORDER BY changedate) prev_date,
lag(t.type) over(PARTITION BY NAME, id ORDER BY changedate) prev_type
FROM test t)
WHERE prev_type IS NOT NULL
就是将数据源表建成一个视图,并在视图的最后加一条记录,改条记录为 select '朱雪霞' name, 75 id, sysdate changedate, null type from dual。这样最后一条就能显示出来了。