各位大神好,我是新人,下面这条语句用时较长:有什么建议能优化一下吗??主要时间耗时在了子查询的 u.userid = t.talkuserid 这一语句中。谢谢大家
select u.*,
(select deptname from EADEPT d where d.deptid = u.deptid) as deptname,
e.roleid,
e.authorizeid,
r.rolename,
c.suggestion as propose,
c.talkresultscore as talksituation,
c.psyexaminescores as checkpsyscore,
c.priexcepscores as checkpriscore,
c.psyexaminesum as checkpsysum,
c.priexcepsum as checkprisum,
c.psyexaminefinish as checkpsyfinish,
c.priexcepfinish as checkprifinish,
(select count(*) from talk_plan t inner join (select offenderid from offender_info) o on t.offenderid =o.offenderid
where u.userid = t.talkuserid AND
t.talktime >= 1451577600000
AND t.talktime <= 1483199999000
and t.state in (10, 20)) as countstalk,
(select count(*) from talk_plan t left join (select offenderid,zklb from offender_info) o on t.offenderid =o.offenderid
where t.state in (10, 20)
and u.userid = t.talkuserid
and (o.zklb in ('1', '2', '3') or appointsign = '1' or plantype = 4)) as alimportanttalk,
(select count(*) from talk_plan t left join (select offenderid,zklb from offender_info) o on t.offenderid =o.offenderid
where t.state in (10, 20, 1)
and u.userid = t.talkuserid
and (o.zklb in ('1', '2', '3') or appointsign = '1' or plantype = 4)) as alltalkcount
from eauser u
inner join (select roleid,authorizeid,userid from eauserauthorize) e on e.userid = u.userid
left join (select roleid,rolename from earole) r on e.roleid = r.roleid
left join checkuserscore c on u.userid = c.userid and c.checkscoretime =1483199999000
where 1 = 1
AND e.roleid in(10000120, 10000124, 10000126, 10000127, 10000128)
select u.*,
(select deptname from EADEPT d where d.deptid = u.deptid) as deptname,
e.roleid,
e.authorizeid,
r.rolename,
c.suggestion as propose,
c.talkresultscore as talksituation,
c.psyexaminescores as checkpsyscore,
c.priexcepscores as checkpriscore,
c.psyexaminesum as checkpsysum,
c.priexcepsum as checkprisum,
c.psyexaminefinish as checkpsyfinish,
c.priexcepfinish as checkprifinish,
(select count(*) from talk_plan t inner join (select offenderid from offender_info) o on t.offenderid =o.offenderid
where u.userid = t.talkuserid AND
t.talktime >= 1451577600000
AND t.talktime <= 1483199999000
and t.state in (10, 20)) as countstalk,
(select count(*) from talk_plan t left join (select offenderid,zklb from offender_info) o on t.offenderid =o.offenderid
where t.state in (10, 20)
and u.userid = t.talkuserid
and (o.zklb in ('1', '2', '3') or appointsign = '1' or plantype = 4)) as alimportanttalk,
(select count(*) from talk_plan t left join (select offenderid,zklb from offender_info) o on t.offenderid =o.offenderid
where t.state in (10, 20, 1)
and u.userid = t.talkuserid
and (o.zklb in ('1', '2', '3') or appointsign = '1' or plantype = 4)) as alltalkcount
from eauser u
inner join (select roleid,authorizeid,userid from eauserauthorize) e on e.userid = u.userid
left join (select roleid,rolename from earole) r on e.roleid = r.roleid
left join checkuserscore c on u.userid = c.userid and c.checkscoretime =1483199999000
where 1 = 1
AND e.roleid in(10000120, 10000124, 10000126, 10000127, 10000128)
解决方案 »
- update 树递归 续
- sql查询的问题.
- 一个oracle 小语句
- [安装]Oracle10.2在Red Hat Enterprise Linux4 Update4 上的详细安装步骤
- 安装了两个客户端,在C#程序中怎么选择某一个来连接数据库?
- 关于oracle建立子表的数据导入
- 用过MS sql server2000 DTS 与oracle导数据和建表的XD进来。。
- 刚入门,问一个简单的sql
- Windows XP professional版下安装oracle9i,可以启动OracleOraHome90ManagementServer么
- 请问各位高手.......................请问在ORACLE里有没有函数可以算日期的差的.
- 不懂就问,oracle的表分区技术
- 服务器报错
对应表的数据量,走的什么连接NL,HASH
走对应的索引了没