1.select * from news where to_char(createtime,'yyyy-mm-dd') = '2008-5-19'
我想到的是把to_char(createtime,'yyyy-mm-dd')建成函数索引,别的没想出来
2.select a.* from emp1 a where a.id not in (select b.id from emp2 b and b.name='roc')
我的改法是select a.* from emp1 a left join emp2 b on a.id=b.id and b.name<>'roc'
3.select a.id,b.*,c.*,d.* from (select a.id,count(name),sum(salary) from a group by a.id) b,a,c,d
where a.id=b.id and b.id=c.id and c.id=d.id and c.name='roc'
这道我是真的不会了
我想到的是把to_char(createtime,'yyyy-mm-dd')建成函数索引,别的没想出来
2.select a.* from emp1 a where a.id not in (select b.id from emp2 b and b.name='roc')
我的改法是select a.* from emp1 a left join emp2 b on a.id=b.id and b.name<>'roc'
3.select a.id,b.*,c.*,d.* from (select a.id,count(name),sum(salary) from a group by a.id) b,a,c,d
where a.id=b.id and b.id=c.id and c.id=d.id and c.name='roc'
这道我是真的不会了
我想到的是把to_char(createtime,'yyyy-mm-dd')建成函数索引,别的没想出来
==》关建索引应该不够,需要改sql,否则索引不使用
select (各个字段名) from news where createtime = to_date('2008-5-19','yyyy-mm-dd')
上面一句,我觉得顺你的思路,改了改。其他两句觉得呢,跟你具体的环境有关,比如表数据的多少。
并且createtime上建索引
2、select a.* from emp1 a where not exists (select b.id from emp2 b where b.name='roc' and b.id=a.id)
where a.id=b.id and b.id=c.id and c.id=d.id 不知道这些表具体怎建的
如果能出相同结果可以考虑一下
直接select xxxx,country(a.xx),sum(a.xx)
from a,b,c,d
where xxx
group by a.id
where a.id=b.id and b.id=c.id and c.id=d.id and c.name='roc'
把c表作为一个子表将条件写在子表里面可以使最后的连接数据减少。其他的应该没什么可以优化的了。
第一句那么写不就是直接一个=就结束了么,为什么要多此一举来个>= 然后再<呢?