时间字段是唯一的吧 select * from table_name where 时间 in ( select 时间 from table_name where 值=0 group by to_char(时间,'YYYY-MM-DD') having count(*)=24 )
加上名称 SELECT * FROM TAB WHERE (TO_CHAR(时间,'YYYY-MM-DD'),名称) NOT IN (SELECT DISTINCT TO_CHAR(时间,'YYYY-MM-DD'),名称 FROM TAB WHERE 值>0)
select t1.* from tab t1, (select trunc(tdate) td, wmsys.wm_concat(num) conc,count(*) cnt from tab group by trunc(tdate) ----把num字段连接起来 ) t2 where trunc(t1.tdate) = (t2.td) and regexp_instr(t2.conc, '1') <= 0 ---时间相等并且不包含1的日期 and t2.cnt = 24
2009-9-23 00 横沙城西3 0
。
。
2009-9-23 23 横沙城西3 0
2009-9-24 00 AA 0
.
.
2009-9-24 17 AA 3
.
2009-9-24 24 AA 2
数据格式就是这样的
只有某天24小时的值都为0才查询出来
WHERE TO_CHAR(时间,'YYYY-MM-DD') NOT IN
(SELECT DISTINCT TO_CHAR(时间,'YYYY-MM-DD') FROM TAB
WHERE 值>0)
2 跨天的24小时算不算?比如2009-9-23 13点到2009-9-24 15点,某名称对应的值都是0。需求明确,写起来才事半功倍。
select *
from table_name
where 时间 in
(
select 时间
from table_name
where 值=0
group by to_char(时间,'YYYY-MM-DD')
having count(*)=24
)
SELECT * FROM TAB
WHERE (TO_CHAR(时间,'YYYY-MM-DD'),名称) NOT IN
(SELECT DISTINCT TO_CHAR(时间,'YYYY-MM-DD'),名称 FROM TAB
WHERE 值>0)
select t1.*
from tab t1,
(select trunc(tdate) td, wmsys.wm_concat(num) conc,count(*) cnt
from tab
group by trunc(tdate) ----把num字段连接起来
) t2
where trunc(t1.tdate) = (t2.td)
and regexp_instr(t2.conc, '1') <= 0 ---时间相等并且不包含1的日期
and t2.cnt = 24