我现在有这么个提取数据需求:我要把table1中的属于某个人(sperson)的在10天内生成(根据makedate)的记录如果多于3条则取出来。
我现在sql语句这么写,不过特别的慢:
select sno,sperson,makedate from table1 a
where
(select count(1) from talbe1 b where b.makedate between a.makedate-10 and a.makedate
and b.sperson=a.sperson
)>=3应该如何优化才能变得飞快呢。表的记录大概有50w左右。
谢谢。
我现在sql语句这么写,不过特别的慢:
select sno,sperson,makedate from table1 a
where
(select count(1) from talbe1 b where b.makedate between a.makedate-10 and a.makedate
and b.sperson=a.sperson
)>=3应该如何优化才能变得飞快呢。表的记录大概有50w左右。
谢谢。
from table1 a
where
a.makedate between a.makedate-10 and a.makedate
group by a.sno
having count(*)>=3
select sno,sperson,makedate from table1 a
where
(select count(1) from talbe1 b where b.makedate between a.makedate-10 and a.makedate
and b.sperson=a.sperson and rownum<=3
)=3
看看是否会快一点
检查执行计划
最好b表能建个makedate和sperson的联合索引