sql 优化 Oracle
<select id="getProposalMainList" parameterType="java.util.Map" resultMap="BaseResultMap"> SELECT main_id, user_id, proposal_name, total_amount, total_fee, create_time, modify_time, create_by, modify_by, state, pdf_url, is_delete, holder_age, holder_sex, holder_social_security, holder_job_detail, holder_name, holder_job_code, holder_job_cate From( SELECT main_id, user_id, proposal_name, total_amount, total_fee, create_time, modify_time, create_by, modify_by, state, pdf_url, is_delete, holder_age, holder_sex, holder_social_security, holder_job_detail, holder_name, holder_job_code, holder_job_cate, row_number() over( ORDER BY create_time DESC ) therow FROM ( SELECT ipm.main_id, ipm.user_id, ipm.proposal_name, ipm.total_amount, ipm.total_fee, ipm.create_time, ipm.modify_time, ipm.create_by, ipm.modify_by, ipm.state, ipm.pdf_url, ipm.is_delete,ipm.holder_age,ipm.holder_sex,ipm.holder_social_security,ipm.holder_job_detail, ipm.holder_name,ipm.holder_job_code,ipm.holder_job_cate,row_number() over( PARTITION BY ipm.main_id ORDER BY ipm.create_time DESC ) RN FROM ipc_proposal_main ipm,IPC_PROPOSAL_MAIN_INFO ipmi , ipc_proposal_risks ipr, lmrisk lmr Where ipm.is_delete = 0 <if test="state != null"> AND ipm.state = #{state} </if> <if test="condition != null"> AND ( ipm.holder_name like concat('%',concat(#{condition},'%')) or (ipmi.insured_name like concat('%',concat(#{condition},'%')) and ipm.main_id = ipmi.main_id and ipmi.is_delete = 0) or (lmr.riskname like concat('%',concat(#{condition},'%')) and ipm.main_id = ipmi.main_id and ipr.pro_id = ipmi.pro_id and ipr.product_code = lmr.riskcode and ipr.is_delete = 0 and ipmi.is_delete = 0 ) ) </if> ) result1 where result1.rn = 1 ) result2 where result2.therow between #{startRow} and #{endRow} </select>
哈哈哈简直是魔鬼吧,不要太耗时
<select id="getProposalMainList" parameterType="java.util.Map" resultMap="BaseResultMap"> SELECT main_id, user_id, proposal_name, total_amount, total_fee, create_time, modify_time, create_by, modify_by, state, pdf_url, is_delete, holder_age, holder_sex, holder_social_security, holder_job_detail, holder_name, holder_job_code, holder_job_cate From( SELECT main_id, user_id, proposal_name, total_amount, total_fee, create_time, modify_time, create_by, modify_by, state, pdf_url, is_delete, holder_age, holder_sex, holder_social_security, holder_job_detail, holder_name, holder_job_code, holder_job_cate, row_number() over( ORDER BY create_time DESC ) therow FROM ( SELECT ipm.main_id, ipm.user_id, ipm.proposal_name, ipm.total_amount, ipm.total_fee, ipm.create_time, ipm.modify_time, ipm.create_by, ipm.modify_by, ipm.state, ipm.pdf_url, ipm.is_delete,ipm.holder_age,ipm.holder_sex,ipm.holder_social_security,ipm.holder_job_detail, ipm.holder_name,ipm.holder_job_code,ipm.holder_job_cate FROM ipc_proposal_main ipm Where ipm.is_delete = 0 <if test="state != null"> AND ipm.state = #{state} </if> <if test="condition != null and condition != ''"> AND exists ( select ipmi.main_id from IPC_PROPOSAL_MAIN_INFO ipmi where ipm.main_id = ipmi.main_id and ipmi.is_delete = 0 AND ipmi.insured_name like '%'||#{condition}||'%' UNION ALL SELECT ipm1.main_id FROM ipc_proposal_main ipm1 where ipm1.holder_name like '%'||#{condition}||'%' AND ipm1.state = 0 AND ipm1.is_delete = 0 AND ipm.main_id= ipm1.main_id UNION ALL select ipmi1.main_id from lmrisk lmr inner join ipc_proposal_risks ipr on ipr.product_code = lmr.riskcode INNER join IPC_PROPOSAL_MAIN_INFO ipmi1 on ipr.pro_id = ipmi1.pro_id where lmr.riskname like '%'||#{condition}||'%' AND ipr.is_delete = 0 and ipmi1.is_delete = 0 AND ipm.main_id = ipmi1.main_id ) </if> ) ) result where result.therow between #{startRow} and #{endRow} </select>