select * from ( select jh,scsj,rq from dba01 where to_char(rq,'yyyy-mm-dd')='2005-06-27' ) a, ( select jh,scsj,max(rq) as rq from dba01 where to_char(rq,'yyyymm')='200505' and scsj>0 group by jh,scsj ) b where a.jh=b.jh
select * from (select jh from dba01 where rq=to_date('20050627','yyyymmdd')) a, (select * from dba01 where (jh,rq) in( select jh,max(rq) from dba01 where rq<to_date('20050531','yyyymmdd') and rq>to_date('20050501','yyyymmdd') and scsj>0 group by jh)) b where a,jh=b.jh;
1:select a.jh from dba01 a where rq = to_date('2005-06-27','yy-mm-dd')2:select b.jh,max(rq) from dba01 b where rq > to_date('2005-05-01','yy-mm-dd') and rq < to_date('2005-05-31','yy-mm-dd') and scsj > 0 group by jh3:select a.jh from dba01 a,(select b.jh,max(rq) from dba01 b where rq > to_date('2005-05-01','yy-mm-dd') and rq < to_date('2005-05-31','yy-mm-dd') and scsj > 0 group by jh ) c where a.rq = to_date('2005-06-27','yy-mm-dd') and a.jh = c.jh
to wfeng7907(无风) 你给的SQL查询速度非常慢,不知道是什么原因呢?-------------------------------------------------to hjyhb(风轻云淡,荣辱不惊) 你给的SQL查询结果有重复数据,请教该如何解决?
如果只需要显示jh的值可以这样 select * from (select jh from dba01 where rq=to_date('20050627','yyyymmdd')) a, (select jh,max(rq) from dba01 where rq between to_date('20050501','yyyymmdd') and to_date('20050531','yyyymmdd') and scsj>0 group by jh) b where a.jh=b.jh;
-------------------------------------------------------------------- 如果真像楼主说得,一天每个井号只有一条记录,那我的怎么会有重复记录呢? 下面作了进一步修改, ------------------------------------------------------------------- select * from ( select jh,scsj,rq from dba01 where to_char(rq,'yyyy-mm-dd')='2005-06-27' ) a, ( select jh,max(scsj),max(rq) as rq from dba01 where to_char(rq,'yyyymm')='200505' and scsj>0 group by jh ) b where a.jh=b.jh
select c.* from dba01 c, (select a.jh,b.max_rq from (select jh from dba01 where trunc(rq)=to_date('2005-06-27','yyyy-mm-dd') group by jh) a, (select jh,max(rq) max_rq from dba01 where trunc(rq,'mm')=to_date('2005-05-01','yyyy-mm-dd') and scsj>0 group by jh) b where a.jh=b.jh ) d where c.jh=d.jh and c.rq=d.rq;
SELECT b.* FROM dba01 b,dba01 a WHERE b.rq BETWEEN to_date('2005-05-01','yyyy-mm-dd') AND to_date('2005-05-31','yyyy-mm-dd') AND b.scsj>0 AND b.rq IN( SELECT MAX(rq) FROM (SELECT jh,rq FROM dba01 GROUP BY jh,rq ORDER BY jh DESC) t GROUP BY jh ) AND a.rq=to_date('2005-05-01','yyyy-mm-dd') AND a.jh=b.jh
(select jh from dba01 where rq=to_date('20050627','yyyymmdd')) a,
(select * from dba01 where (jh,rq) in(
select jh,max(rq) from dba01 where rq<to_date('20050531','yyyymmdd') and rq>to_date('20050501','yyyymmdd') and scsj>0 group by jh)) b where a,jh=b.jh;
and rq < to_date('2005-05-31','yy-mm-dd')
and scsj > 0
group by jh3:select a.jh from dba01 a,(select b.jh,max(rq) from dba01 b where rq > to_date('2005-05-01','yy-mm-dd')
and rq < to_date('2005-05-31','yy-mm-dd')
and scsj > 0
group by jh
) c
where a.rq = to_date('2005-06-27','yy-mm-dd')
and a.jh = c.jh
select * from
(select jh from dba01 where rq=to_date('20050627','yyyymmdd')) a,
(select jh,max(rq) from dba01 where rq between to_date('20050501','yyyymmdd') and to_date('20050531','yyyymmdd') and scsj>0 group by jh) b where a.jh=b.jh;
如果真像楼主说得,一天每个井号只有一条记录,那我的怎么会有重复记录呢?
下面作了进一步修改,
-------------------------------------------------------------------
select * from
(
select jh,scsj,rq from dba01 where to_char(rq,'yyyy-mm-dd')='2005-06-27'
) a,
(
select jh,max(scsj),max(rq) as rq from dba01 where to_char(rq,'yyyymm')='200505' and scsj>0
group by jh
) b
where a.jh=b.jh
(select a.jh,b.max_rq from
(select jh
from dba01
where trunc(rq)=to_date('2005-06-27','yyyy-mm-dd')
group by jh) a,
(select jh,max(rq) max_rq
from dba01
where trunc(rq,'mm')=to_date('2005-05-01','yyyy-mm-dd')
and scsj>0
group by jh) b
where a.jh=b.jh
) d
where c.jh=d.jh and c.rq=d.rq;
BETWEEN to_date('2005-05-01','yyyy-mm-dd')
AND to_date('2005-05-31','yyyy-mm-dd')
AND b.scsj>0
AND b.rq
IN(
SELECT MAX(rq) FROM (SELECT jh,rq FROM dba01 GROUP BY jh,rq ORDER BY jh DESC) t GROUP BY jh
)
AND a.rq=to_date('2005-05-01','yyyy-mm-dd') AND a.jh=b.jh