oracle子查询和链接查询,Oracle使用子查询，和左连接查询同样结果性能差距。 | 学步园...

这条SQL，执行，查询所有员工当月的考勤记录汇总，展示全部数据耗时2.3秒

String sql = "select userid,orgid," +子查询，性能太慢，不适用

"(select username from t_acl_userinfo where userid=t.userid) username," +

"(select count(state) from t_chk_manage where userid=t.userid and state='01'"+sqlappend+") state01 ," +

"(select count(state) from t_chk_manage where userid=t.userid and state='02'"+sqlappend+") state02 ," +

"(select count(state) from t_chk_manage where userid=t.userid and state='03'"+sqlappend+") state03 ," +

"(select count(state) from t_chk_manage where userid=t.userid and state='04'"+sqlappend+") state04 ," +

"(select count(state) from t_chk_manage where userid=t.userid and state='05'"+sqlappend+") state05 ," +

"(select count(state) from t_chk_manage where userid=t.userid and state='06'"+sqlappend+") state06 ," +

"(select count(state) from t_chk_manage where userid=t.userid and state='07'"+sqlappend+") state07 ," +

"(select count(state) from t_chk_manage where userid=t.userid and state='08'"+sqlappend+") state08 ," +

"(select count(state) from t_chk_manage where userid=t.userid and state='09'"+sqlappend+") state09 " +

"from t_chk_manage t group by userid,orgid HAVING 1=1";

//使用左连接，先查出基本数据，再连接， Oracle 耗时0.2秒(速度提升将近10倍)

String sql = "select s.* , userInfo.username as username , state1.state as state01 , state2.state as state02 , state3.state as state03 , state4.state as state04 , " +

"state5.state as state05 , state6.state as state06 , state7.state as state07 , state8.state as state08 , state9.state as state09 " +

"from (select userid,orgid from t_chk_manage t where 1=1 "+dataSearch+"  group by userid , orgid HAVING 1=1 "+search+") s " +

"left join (select username,userid from t_acl_userinfo)userInfo on userInfo.userid = s.userid "+

"left join (select userid,count(state) as state from t_chk_manage where state = '01'  "+dataSearch+" group by userid)state1 on state1.userid = s.userid " +

"left join (select userid,count(state) as state from t_chk_manage where state = '02'  "+dataSearch+" group by userid)state2 on state2.userid = s.userid " +

"left join (select userid,count(state) as state from t_chk_manage where state = '03'  "+dataSearch+" group by userid)state3 on state3.userid = s.userid " +

"left join (select userid,count(state) as state from t_chk_manage where state = '04'  "+dataSearch+" group by userid)state4 on state4.userid = s.userid " +

"left join (select userid,count(state) as state from t_chk_manage where state = '05'  "+dataSearch+" group by userid)state5 on state5.userid = s.userid " +

"left join (select userid,count(state) as state from t_chk_manage where state = '06'  "+dataSearch+" group by userid)state6 on state6.userid = s.userid " +

"left join (select userid,count(state) as state from t_chk_manage where state = '07'  "+dataSearch+" group by userid)state7 on state7.userid = s.userid " +

"left join (select userid,count(state) as state from t_chk_manage where state = '08'  "+dataSearch+" group by userid)state8 on state8.userid = s.userid " +

"left join (select userid,count(state) as state from t_chk_manage where state = '09'  "+dataSearch+" group by userid)state9 on state9.userid = s.userid";

//这条sql查询当月员工考勤明细记录，耗时0.4秒，(原本使用子查询需要7秒，提升20倍)

select day0.*,username,"+dayAppend+" from (select userid,orgid,substr(t.create_time,0,7) from t_chk_manage t where substr(t.create_time,0,7)='"+daystart.substring(0, 7)+"' " +

"group by userid,orgid,substr(t.create_time,0,7) HAVING 1=1 "+search+")day0 " +

"left join (select userid,username from t_acl_userinfo)username on username.userid = day0.userid " +

""+sqlAppend