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
解决方案 »
- sql2005通过odbc向oracle中insert数据报下列错误,请高手指教!
- 向各位oracle大牛们求救一个sql问题
- 求oracle 访问 web services 办法
- 求一sql语句
- 这样一个语句会提示标识过长select BUREAUINFO from PM_REALTIME_HLR_ERR
- oracle9i,我在oracle enterprise manager console中建了个用户??
- 有谁用pro*c篇写过能执行任何查询语句的东东?
- 急:事务提交,居然后面的执行成功,前面的没有执行。
- 请教从SQL SERVER复制一个表ORACLE,该怎么办呢?焦急的在线等待中
- 如何在delphi中得到存储过程的返回的数据集(在线等待中)
- UPDATE语句应如何写?
- 这条函数不能用??为什么
(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