select a,b from t where d>trunc(sysdate) group by a,b having count(*)>1;
楼主,是不是找出今天,状态先为Y后为N的用户记录?这样行不行: select Y.a,Y.b,Y.max_d,N.min_d from (select a,b,max(d) max_d from t where d>trunc(sysdate) and f='Y' group by a,b) Y, (select a,b,min(d) min_d from t where d>trunc(sysdate) and f='N' group by a,b) Nwhere Y.max_d<=N.min_d and Y.a=N.a and Y.b=N.b;
楼主,是不是找出今天,状态先为Y后为N的用户记录?这样行不行: select Y.a,Y.b,Y.max_d,N.min_d from (select a,b,max(d) max_d from t where d>trunc(sysdate) and f='Y' group by a,b) Y, (select a,b,min(d) min_d from t where d>trunc(sysdate) and f='N' group by a,b) Nwhere Y.max_d<=N.min_d and Y.a=N.a and Y.b=N.b;
select a,b from t t1 where f ='Y' and trunc(d) = trunc(sysdate) and exists(select 1 from t t2 where t2.a=t1.a and t2.b=t1.b and t2.d>t1.d and t2.f='N')
select a,b from t t1 where f ='Y' and trunc(d) = trunc(sysdate) and exists(select 1 from t t2 where t2.a=t1.a and t2.b=t1.b and t2.d>t1.d and t2.f='N' and trunc(t2.d) = trunc(sysdate))
select WW.USERNAME from (select a||b AS USERNAME from t where t=to_char(sysdate,'yyyymmdd') and f='N' ) WW, (select a||b AS USERNAME from t where t=to_char(sysdate,'yyyymmdd') and f='Y' ) XX WHERE WW.USERNAME=XX.USERNAME
where d>trunc(sysdate) group by a,b having count(*)>1;
select Y.a,Y.b,Y.max_d,N.min_d
from
(select a,b,max(d) max_d
from t
where d>trunc(sysdate)
and f='Y'
group by a,b) Y, (select a,b,min(d) min_d
from t
where d>trunc(sysdate)
and f='N'
group by a,b) Nwhere Y.max_d<=N.min_d
and Y.a=N.a
and Y.b=N.b;
select Y.a,Y.b,Y.max_d,N.min_d
from
(select a,b,max(d) max_d
from t
where d>trunc(sysdate)
and f='Y'
group by a,b) Y, (select a,b,min(d) min_d
from t
where d>trunc(sysdate)
and f='N'
group by a,b) Nwhere Y.max_d<=N.min_d
and Y.a=N.a
and Y.b=N.b;
where f ='Y'
and trunc(d) = trunc(sysdate)
and exists(select 1 from t t2 where t2.a=t1.a and t2.b=t1.b and t2.d>t1.d and t2.f='N')
where f ='Y'
and trunc(d) = trunc(sysdate)
and exists(select 1 from t t2 where t2.a=t1.a and t2.b=t1.b and t2.d>t1.d and t2.f='N' and trunc(t2.d) = trunc(sysdate))
(select a||b AS USERNAME from t
where t=to_char(sysdate,'yyyymmdd')
and f='N' ) WW,
(select a||b AS USERNAME from t
where t=to_char(sysdate,'yyyymmdd')
and f='Y' ) XX
WHERE WW.USERNAME=XX.USERNAME
如果今天的记录有100条,前99条一直是Y,最后一条是N,那么这么查出来的就有99条,而实际应该是查出有Y-->N的记录。应该是99+100那两条记录,对不?