我写的SQL如下
SELECT
tall.LOCATION_ID,
tall.LOCATION_NAME,
tall.JOB_ID,
tall.JOB_NAME,
tall.NEGOTIABLE_FLAG,
tall.SALARY_MAX,
tall.SALARY_MIN,
tall.ADD_TIME,
tall.ENTERPRISE_ID,
tall.ENTERPRISE_NAME,
tall.ENTERPRISE_LOGO,
tall.WORK_YEARS_ID,
tall.WORK_YEARS_INFO,
IFNULL( tujf.FOCUS_FLAG,0) as flag
FROM
(SELECT
tj.JOB_ID,
tj.LOCATION_ID,
tl.LOCATION_NAME,
tj.JOB_NAME,
tj.NEGOTIABLE_FLAG,
tj.SALARY_MAX,
tj.SALARY_MIN,
tj.ADD_TIME,
tj.ENTERPRISE_ID,
te.ENTERPRISE_NAME,
te.ENTERPRISE_LOGO,
twy.WORK_YEARS_ID,
twy.WORK_YEARS_INFO
FROM
t_job
tj,t_location tl,t_enterprise te,t_hr th,t_work_years
twy,t_job_category tjc,t_job_type tjt,t_salary ts
WHERE tj.LOCATION_ID
= tl.LOCATION_ID
AND tl.LOCATION_LEVEL = 2
AND tl.USE_FLAG = 1
AND
tj.ENTERPRISE_ID = te.ENTERPRISE_ID
AND te.USE_FLAG = 1
AND tj.HR_ID =
th.HR_ID
AND th.USE_FLAG = 1
AND tj.WORK_YEARS_ID = twy.WORK_YEARS_ID
AND tj.JOB_CATEGORY_ID = tjc.JOB_CATEGORY_ID
AND tjc.USE_FLAG = 1
AND
tj.JOB_TYPE_ID = tjt.JOB_TYPE_ID
AND tjt.USE_FLAG = 1
AND ts.SALARY_ID =
#{salaryId}
AND ts.SALARY_MAX >=tj.SALARY_MAX
AND ts.SALARY_MIN <=
tj.SALARY_MIN
<if test="jobCategoryId != null">
AND
tjc.JOB_CATEGORY_ID = #{jobCategoryId}
</if>
<if test="locationId != null">
AND
tl.LOCATION_ID = #{locationId}
</if>
<if test="workYearId != null">
AND
twy.WORK_YEARS_ID = #{workYearId}
</if>
<if test="jobTypeId != null">
AND
tjt.JOB_TYPE_ID = #{jobTypeId}
</if>
<if test="lastTime != null">
AND
tj.ADD_TIME <= #{lastTime}
</if>
GROUP BY
tj.JOB_ID
ORDER BY
tj.ADD_TIME DESC) tall
left join
(select JOB_ID
as
FOCUS_JOB_ID,‘1‘ as FOCUS_FLAG from
t_user_job_focus where
user_id =
#{userId} and TYPE = ‘01‘) tujf
on
tall.JOB_ID = tujf.FOCUS_JOB_ID
LIMIT
#{pageIndex}
优化后的SQL如下
SELECT
tallTwo.JOB_ID,
tallTwo.LOCATION_ID,
tallTwo.LOCATION_NAME,
tallTwo.JOB_NAME,
tallTwo.NEGOTIABLE_FLAG,
tallTwo.SALARY_MAX,
tallTwo.SALARY_MIN,
tallTwo.ADD_TIME,
tallTwo.ENTERPRISE_ID,
tallTwo.ENTERPRISE_NAME,
tallTwo.ENTERPRISE_LOGO,
tallTwo.WORK_YEARS_ID,
tallTwo.WORK_YEARS_INFO,
tallTwo.HR_ID,
tallTwo.JOB_CATEGORY_ID,
IFNULL( tujf.FOCUS_FLAG,0) as flag
FROM
(SELECT
tall.JOB_ID,
tall.LOCATION_ID,
tl.LOCATION_NAME,
tall.JOB_NAME,
tall.NEGOTIABLE_FLAG,
tall.SALARY_MAX,
tall.SALARY_MIN,
tall.ADD_TIME,
tall.ENTERPRISE_ID,
te.ENTERPRISE_NAME,
te.ENTERPRISE_LOGO,
tall.WORK_YEARS_ID,
twy.WORK_YEARS_INFO,
tall.HR_ID,
tall.JOB_CATEGORY_ID,
tall.JOB_TYPE_ID
FROM
(
SELECT
tj.JOB_ID,
tj.LOCATION_ID,
tj.JOB_NAME,
tj.NEGOTIABLE_FLAG,
tj.SALARY_MAX,
tj.SALARY_MIN,
tj.ADD_TIME,
tj.ENTERPRISE_ID,
tj.WORK_YEARS_ID,
tj.HR_ID,
tj.JOB_CATEGORY_ID,
tj.JOB_TYPE_ID
FROM
t_job tj
<if test="salaryId != null">
,t_salary ts
</if>
WHERE
1 = 1
<if test="salaryId != null">
AND ts.SALARY_ID = #{salaryId}
AND ts.SALARY_MAX
>=tj.SALARY_MAX
AND ts.SALARY_MIN <= tj.SALARY_MIN
</if>
<if test="locationId != null">
AND
tj.LOCATION_ID = #{locationId}
</if>
<if test="workYearId != null">
AND
tj.WORK_YEARS_ID = #{workYearId}
</if>
<if test="jobCategoryId != null">
AND
tj.JOB_CATEGORY_ID =
#{jobCategoryId}
</if>
<if test="jobTypeId != null">
AND
tj.JOB_TYPE_ID = #{jobTypeId}
</if>
<if test="lastTime != null">
AND
tj.ADD_TIME <= #{lastTime}
</if>
GROUP BY tj.JOB_ID
ORDER BY tj.ADD_TIME DESC
) tall
LEFT JOIN t_location
tl ON tall.LOCATION_ID = tl.LOCATION_ID
AND tl.LOCATION_LEVEL = 2
AND
tl.USE_FLAG = 1
LEFT JOIN t_enterprise te ON tall.ENTERPRISE_ID =
te.ENTERPRISE_ID
AND te.USE_FLAG = 1
LEFT JOIN t_hr th ON tall.HR_ID =
th.HR_ID
AND th.USE_FLAG = 1
LEFT JOIN t_work_years twy ON
tall.WORK_YEARS_ID = twy.WORK_YEARS_ID
LEFT JOIN t_job_category tjc ON
tall.JOB_CATEGORY_ID = tjc.JOB_CATEGORY_ID
AND tjc.USE_FLAG = 1
LEFT JOIN t_job_type tjt ON
tall.JOB_TYPE_ID = tjt.JOB_TYPE_ID
AND tjt.USE_FLAG = 1)
tallTwo
LEFT JOIN (select JOB_ID as FOCUS_JOB_ID,‘1‘ as FOCUS_FLAG from
t_user_job_focus where user_id = #{userId} and TYPE = ‘01‘) tujf
ON
tallTwo.JOB_ID = tujf.FOCUS_JOB_ID
LIMIT #{pageIndex}
思路对比
我的思路：先关联所有需要的表查出所有的数据，再根据条件过滤。
项目经理思路：先查出基本数据，根据条件过滤，再关联需要的表。
原文：http://my.oschina.net/u/2312022/blog/519000