Oracle uses a subquery, which has the same performance difference as the left join query.

This SQL statement is executed to query the total attendance records of all employees for the current month. It takes 2.3 seconds to display all the data.

String SQL = "select userid, orgid," + subquery, slow performance, not applicable
"(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 ";

// Use left join. It takes 0.2 seconds to first find the basic data and connect to Oracle (the speed is increased by nearly 10 times)

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 ";

// This SQL statement queries the employee's attendance details for the current month, which takes 0.4 seconds (originally It took 7 seconds to use the subquery, which is increased by 20 times)

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