<!-- 前台-职位搜索-分页查询 -->
<select id="getJobSearchByPage" parameterType=""
resultType="">
SELECT temp.* from
( <!-- 1-全国置顶 -->
(
SELECT 1 as ord,<include refid="JobSearch_Column_List" /> FROM
t_job AS t
LEFT JOIN t_user u ON = t.user_id
LEFT JOIN t_user_company c ON c.user_id = t.user_id
WHERE 1 = 1
<include refid="JobSearch_condition_List" />
and t.roof_place = 1 and t.roof_place_type = 1 order by t.roof_refresh_time desc,t.roof_place_time desc,t.refresh_time desc
)
<!-- 2-本省置顶 -->
UNION ALL
(
SELECT 2 as ord,<include refid="JobSearch_Column_List" /> FROM
t_job AS t
LEFT JOIN t_user u ON = t.user_id
LEFT JOIN t_user_company c ON c.user_id = t.user_id
WHERE 1 = 1
<include refid="JobSearch_condition_List" />
and t.roof_place = 1 and t.roof_place_type = 2 order by t.roof_refresh_time desc,t.roof_place_time desc,t.refresh_time desc
)
<!-- 3-本市置顶 -->
UNION ALL
(
SELECT 3 as ord,<include refid="JobSearch_Column_List" /> FROM
t_job AS t
LEFT JOIN t_user u ON = t.user_id
LEFT JOIN t_user_company c ON c.user_id = t.user_id
WHERE 1 = 1
<include refid="JobSearch_condition_List" />
and t.roof_place = 1 and t.roof_place_type = 3 order by t.roof_refresh_time desc,t.roof_place_time desc,t.refresh_time desc
)
<!-- 4-区县置顶 -->
UNION ALL
(
SELECT 4 as ord,<include refid="JobSearch_Column_List" /> FROM
t_job AS t
LEFT JOIN t_user u ON = t.user_id
LEFT JOIN t_user_company c ON c.user_id = t.user_id
WHERE 1 = 1
<include refid="JobSearch_condition_List" />
and t.roof_place = 1 and t.roof_place_type = 4 order by t.roof_refresh_time desc,t.roof_place_time desc,t.refresh_time desc
)
<!-- 普通未置顶的职位 -->
UNION ALL
(
SELECT 5 as ord,<include refid="JobSearch_Column_List" /> FROM
t_job AS t
LEFT JOIN t_user u ON = t.user_id
LEFT JOIN t_user_company c ON c.user_id = t.user_id
WHERE 1 = 1
<include refid="JobSearch_condition_List" />
and t.roof_place = 0 order by t.refresh_time desc, desc
)
) temp where 1=1
<!-- 排队方式：1 默认；2-最新 -->
<if test="c_orderType != null and c_orderType == 1 ">
order by asc,temp.refresh_time desc
</if>
<if test="c_orderType != null and c_orderType == 2 ">
order by desc
</if>
</select>
<!-- 前台-职位搜索-查询结果列 -->
<sql id="JobSearch_Column_List">
,
,
t.refresh_time,
,
t.user_id AS userId,
AS title,
t.job_nature AS jobNature,
t.salary_name AS salaryName,
t.job_classify_name as jobClassifyName,
t.job_type_name AS jobTypeName,
t.province_name AS provinceName,
t.city_name AS cityName,
t.area_name AS areaName,
t.job_duty AS jobDuty,
t.job_require AS jobRequire,
t.working_seniority AS workingSeniority,
t.academic_require,
c.logo_image AS logoImage,
c.`name` AS companyName,
c.industry_name AS industryName,
c.speciality_name AS specialityName,
c.company_qualification AS companyQualification,
AS contactPhoto,
,
c.contact_post AS contactPost,
,
t.roof_place as roofPlace
</sql>
<!-- 前台-职位搜索-查询条件列 -->
<sql id="JobSearch_condition_List">
AND t.is_del = 0 and = 1 and t.urgent_job = 0
<if test="c_jobNature != null and c_jobNature == 1 ">
and t.job_nature = 0 <!-- 全职 -->
</if>
<if test="c_jobNature != null and c_jobNature == 2 ">
and t.job_nature = 1 <!-- 兼职 -->
</if>
<!-- 地区 -->
<if test="c_provinceCode != null ">
and t.province_code = ${c_provinceCode}
</if>
<if test="c_cityCode != null ">
and t.city_code = ${c_cityCode}
</if>
<if test="c_areaCode != null ">
and t.area_code = ${c_areaCode}
</if>
<!-- 行业 -->
<if test="c_industryId != null ">
and t.industry_id = #{c_industryId}
</if>
<if test="c_specialityId != null ">
and t.speciality_id = #{c_specialityId}
</if>
<!-- 职位 -->
<if test="c_jobClassifyId != null ">
and t.job_classify_id = #{c_jobClassifyId}
</if>
<if test="c_jobTypeId != null ">
and t.job_type_id = #{c_jobTypeId}
</if>
<if test="c_salaryId != null ">
and t.salary_id = #{c_salaryId}
</if>
<if test="searchKey != null and searchKey != '' ">
and ( like "%"#{searchKey}"%" or c.`name` like "%"#{searchKey}"%")
</if>
</sql>