select * from tb where f1>0 and 日期>to_date('2009-03-02 00:00:00','yyyy-mm-dd hh24:mm:ss') ;
select * from table_name b where 日期>to_date('2009-03-02 00:00:00','yyyy-mm-dd hh24:mm:ss') and not exists (select 'X' from table_name a from a.日期=b.日期 and f1<=0 )
速度不是一般的慢设数据库结构是这样: n f rq
要求这样的查询结果,rq大于某一天,n对应的f每天都大于0,得出这样的记录
设数据库结构是这样: n f rq a1 -1 2009-3-1 a1 1 2009-3-2 a2 2 2009-3-1 a2 3 2009-3-2假设日期是3月1号以后的,a2可以获取到,a1就不可以
select * from t a where rq>... and not exists(select 1 from t b where b.rq>同上面 and b.f<0 and a.n=b.n); 或者 select * from t a where rq>... and not exists(select 1 from t b where b.f<0 and a.n=b.n); 用not in或not exists没关系。如果慢,看索引如何建的? 如果F区分度好,在rq和f上分别建索引,第二个SQL即可。 否则建rq,f联合索引,用第一个sql.
SELECT f1, SUM(f1) OVER(PARTITION BY n ) HJ FROM talbe WHERE RQ > to_date('2009-01-02','yyyy-mm-dd') and f1>0 minusSELECT SELECT f1, SUM(f1) OVER(PARTITION BY n ) HJ FROM talbe WHERE RQ > to_date('2009-01-02','yyyy-mm-dd') and f1<=0
SELECT f1, SUM(f1) OVER(PARTITION BY n ) HJ FROM talbe WHERE RQ > to_date('2009-01-02','yyyy-mm-dd') and f1>0 minus SELECT f1, SUM(f1) OVER(PARTITION BY n ) HJ FROM talbe WHERE RQ > to_date('2009-01-02','yyyy-mm-dd') and f1 <=0
from tb
where f1>0 and 日期>to_date('2009-03-02 00:00:00','yyyy-mm-dd hh24:mm:ss')
;
from table_name b
where 日期>to_date('2009-03-02 00:00:00','yyyy-mm-dd hh24:mm:ss')
and not exists (select 'X' from table_name a from a.日期=b.日期 and f1<=0 )
n f rq
要求这样的查询结果,rq大于某一天,n对应的f每天都大于0,得出这样的记录
n f rq
a1 -1 2009-3-1
a1 1 2009-3-2
a2 2 2009-3-1
a2 3 2009-3-2假设日期是3月1号以后的,a2可以获取到,a1就不可以
where rq>...
and not exists(select 1 from t b
where b.rq>同上面
and b.f<0
and a.n=b.n);
或者
select * from t a
where rq>...
and not exists(select 1 from t b
where b.f<0
and a.n=b.n);
用not in或not exists没关系。如果慢,看索引如何建的?
如果F区分度好,在rq和f上分别建索引,第二个SQL即可。
否则建rq,f联合索引,用第一个sql.
SELECT
f1,
SUM(f1) OVER(PARTITION BY
n ) HJ
FROM talbe
WHERE
RQ > to_date('2009-01-02','yyyy-mm-dd')
and f1>0
minusSELECT
SELECT
f1,
SUM(f1) OVER(PARTITION BY
n ) HJ
FROM talbe
WHERE
RQ > to_date('2009-01-02','yyyy-mm-dd')
and f1<=0
SELECT
f1,
SUM(f1) OVER(PARTITION BY
n ) HJ
FROM talbe
WHERE
RQ > to_date('2009-01-02','yyyy-mm-dd')
and f1>0
minus SELECT
f1,
SUM(f1) OVER(PARTITION BY
n ) HJ
FROM talbe
WHERE
RQ > to_date('2009-01-02','yyyy-mm-dd')
and f1 <=0