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