select c.funid, count(c.funid) as cf , decode((Select count(t.userid)
from B_ANHUI_CLIENT_USE_DETAIL t join t_cust cust on trim(t.userid) = trim(cust.custid)
where funid = 'MDSP00001' and substr(t.createdate, 0, 10) >='2014-05-04' and '2014-05-04'>=substr(t.createdate, 0, 10)
and t.userid = '(null)'
Group By t.userid),Null, count(Distinct c.userid) ,(count(Distinct c.userid) + (Select count(t.userid)
from B_ANHUI_CLIENT_USE_DETAIL t join t_cust cust on trim(t.userid) = trim(cust.custid)
where funid = 'MDSP00001' and substr(t.createdate, 0, 10) >='2014-05-04' and '2014-05-04'>=substr(t.createdate, 0, 10)
and t.userid = '(null)'
Group By t.userid )- 1) )as cd
, Count(Distinct c.userid)As ck
from B_ANHUI_CLIENT_USE_DETAIL c
where
(to_date(substr(c.createdate, 0, 10), 'yyyy-mm-dd') >= to_date('2014-05-04','yyyy-mm-dd') and to_date('2014-05-04','yyyy-mm-dd')>=to_date(substr(c.createdate, 0, 10), 'yyyy-mm-dd') )
GROUP BY c.FUNID
函数用的太多没法建索引,建了函数索引也没效果,不知道是不是没走函数索引。现在这SQL执行完大概31秒,请高手优化!
不用了 ,解决了,建了个函数索引 速度变成0.4秒了
仔细看SQL,说点干货,解决问题!
追答不是高手,因为刚今天帮忙优化过一个简单的sql语句,所以建议你看下sql优化的资料这些。