17,082
社区成员
发帖
与我相关
我的任务
分享
--1.创建临时表
select '张三' XM, 'A' LX,'20170601' RQ,'XX01' ZL,88 SL, '修改过' BZ from dual union all
select '张三' XM, 'B' LX,'20170601' RQ,'XX02' ZL,53 SL, '修改过' BZ from dual union all
select '张三' XM, 'C' LX,'20170601' RQ,'XX03' ZL,77 SL, '修改过' BZ from dual union all
select '张三' XM, 'A' LX,'20170901' RQ,'XX01' ZL,87 SL, '修改过' BZ from dual union all
select '张三' XM, 'B' LX,'20170901' RQ,'XX02' ZL,50 SL, '修改过' BZ from dual union all
select '张三' XM, 'C' LX,'20170901' RQ,'XX03' ZL,70 SL, '修改过' BZ from dual union all
select '张三' XM, 'A' LX,'20180601' RQ,'XX01' ZL,86 SL, NULL BZ from dual union all
select '张三' XM, 'B' LX,'20180601' RQ,'XX02' ZL,53 SL, NULL BZ from dual union all
select '张三' XM, 'C' LX,'20180601' RQ,'XX03' ZL,72 SL, NULL BZ from dual union all
select '李四' XM, 'A' LX,'20170601' RQ,'XX03' ZL,90 SL, NULL BZ from dual union all
select '李四' XM, 'B' LX,'20170601' RQ,'XX04' ZL,65 SL, NULL BZ from dual
--1.创建临时表
create table tmp as
select '张三' xm, 'a' lx,'20170601' rq,'xx01' zl,88 sl, '修改过' bz from dual union all
select '张三' xm, 'b' lx,'20170601' rq,'xx02' zl,53 sl, '修改过' bz from dual union all
select '张三' xm, 'c' lx,'20170601' rq,'xx03' zl,77 sl, '修改过' bz from dual union all
select '张三' xm, 'a' lx,'20170901' rq,'xx01' zl,87 sl, '修改过' bz from dual union all
select '张三' xm, 'b' lx,'20170901' rq,'xx02' zl,50 sl, '修改过' bz from dual union all
select '张三' xm, 'c' lx,'20170901' rq,'xx03' zl,70 sl, '修改过' bz from dual union all
select '张三' xm, 'a' lx,'20180601' rq,'xx01' zl,86 sl, null bz from dual union all
select '张三' xm, 'b' lx,'20180601' rq,'xx02' zl,53 sl, null bz from dual union all
select '张三' xm, 'c' lx,'20180601' rq,'xx03' zl,72 sl, null bz from dual union all
select '李四' xm, 'a' lx,'20170601' rq,'xx03' zl,90 sl, null bz from dual union all
select '李四' xm, 'b' lx,'20170601' rq,'xx04' zl,65 sl, null bz from dual
--2.SQL实现
select xm, lx,rq,zl,sl,bz
from(select xm, lx,rq,zl,sl,bz,row_number()over(partition by xm,lx order by rq,bz desc) rn
from tmp
)
where rn = 1
select * from t1 where (xm,rq) in (select xm,rq from(
select a.*,row_numer()over(partition by xm,rq order by rq asc) rn from t1 a where bz='修改过') m1 where m1.rn=1)
union all
select * from t1 where xm,rq in
(select xm,rq from t1 where xm,rq not in(select xm,rq from t1 where bz='修改过')