select count(decode(to_char(x.lr_sj,'MM') , '01', '1', null)) one1,
count(decode(to_char(x.lr_sj,'MM') , '02', '1', null)) two2,
count(decode(to_char(x.lr_sj,'MM') , '03', '1', null)) three,
count(decode(to_char(x.lr_sj,'MM') , '04', '1', null)) four,
count(decode(to_char(x.lr_sj,'MM') , '05', '1', null)) five,
count(decode(to_char(x.lr_sj,'MM') , '06', '1', null)) six,
count(decode(to_char(x.lr_sj,'MM') , '07', '1', null)) seven,
count(decode(to_char(x.lr_sj,'MM') , '08', '1', null)) eight,
count(decode(to_char(x.lr_sj,'MM') , '09', '1', null)) nine,
count(decode(to_char(x.lr_sj,'MM') , '10', '1', null)) ten,
count(decode(to_char(x.lr_sj,'MM') , '11', '1', null)) eleven,
count(decode(to_char(x.lr_sj,'MM') , '12', '1', null)) twelve
from yw_jcdjxx x where to_char(x.lr_sj,'yyyy')=to_char(sysdate,'yyyy') 意思就是查出每月登记的数目,我这样写不知道效率好不好?请各位大虾门多指教,有没有更好的写法?谢谢
count(decode(to_char(x.lr_sj,'MM') , '02', '1', null)) two2,
count(decode(to_char(x.lr_sj,'MM') , '03', '1', null)) three,
count(decode(to_char(x.lr_sj,'MM') , '04', '1', null)) four,
count(decode(to_char(x.lr_sj,'MM') , '05', '1', null)) five,
count(decode(to_char(x.lr_sj,'MM') , '06', '1', null)) six,
count(decode(to_char(x.lr_sj,'MM') , '07', '1', null)) seven,
count(decode(to_char(x.lr_sj,'MM') , '08', '1', null)) eight,
count(decode(to_char(x.lr_sj,'MM') , '09', '1', null)) nine,
count(decode(to_char(x.lr_sj,'MM') , '10', '1', null)) ten,
count(decode(to_char(x.lr_sj,'MM') , '11', '1', null)) eleven,
count(decode(to_char(x.lr_sj,'MM') , '12', '1', null)) twelve
from yw_jcdjxx x where to_char(x.lr_sj,'yyyy')=to_char(sysdate,'yyyy') 意思就是查出每月登记的数目,我这样写不知道效率好不好?请各位大虾门多指教,有没有更好的写法?谢谢
数据量不大,而且以后也不大的话这样就可以
如果之前已经基于lr_sj建立了索引,那这样做to_char(x.lr_sj,'yyyy')转换的话,索引是不会被调用的。
select部分既然你要做转换,那效率上应该提高不了多少。
但Where条件里面可以按以下两种思路来做:
1.
x.lr_sj >(这里面就不给你写了,应该是某年开始那天0分0秒,到该看结束那天59分59秒):
2.
可以基于to_char(x.lr_sj,'yyyy')上面做一个函数索引,但这种不建议做,因为等数据量多的话,数据库对于索引的维护压力会很大