select j.tm,j.*,t.*, g.ORG_NM, g.ORG_SHNM
from ST_GRW_REGMON_R j
left join ST_HDGSTBPRP_B t
on j.STCD = t.STCD
left join ST_ADMA_B g
on t.ENG_MAN_CD = g.ORG_CD
where 1 = 1
-- and j.tm >= to_date('2014-07-20 00', 'yyyy-mm-dd HH24')
-- and j.tm <= to_date('2014-07-20 12', 'yyyy-mm-dd HH24')
and to_char(j.tm,'yyyy-MM-dd HH24:mi:ss') in ( select distinct(to_char(j.tm,'yyyy-MM-dd HH24'))||':00:00'
from ST_GRW_REGMON_R j
from ST_GRW_REGMON_R j
left join ST_HDGSTBPRP_B t
on j.STCD = t.STCD
left join ST_ADMA_B g
on t.ENG_MAN_CD = g.ORG_CD
where 1 = 1
-- and j.tm >= to_date('2014-07-20 00', 'yyyy-mm-dd HH24')
-- and j.tm <= to_date('2014-07-20 12', 'yyyy-mm-dd HH24')
and to_char(j.tm,'yyyy-MM-dd HH24:mi:ss') in ( select distinct(to_char(j.tm,'yyyy-MM-dd HH24'))||':00:00'
from ST_GRW_REGMON_R j
where 1=1
and j.tm >= to_date('2014-07-20 00', 'yyyy-mm-dd HH24')
and j.tm <= to_date('2014-07-20 12', 'yyyy-mm-dd HH24'))order by j.tm desc, t.stcd
标红部分为查询整点条件,如有更好的方式贴出来互相学习。谢谢
经人指点很有种很简单的方式:
and to_char (j.tm,'mi') =0
and to_char (j.tm,'ss') =0
用to_char将日期传成char,只取mi和ss。