MySQL函数 FIND_IN_SET 实现多条件搜索
一、目标
想实现如下 去哪儿网 的一个多条件搜索功能，就是勾选了上面的条件，下面的内容就根据上面勾选条件自动选择展示......
二、前端
1、html 文件
1 <div class="choose" style="margin-left: 1%;color: #0a0a0a"> 2 <p class="currentStatus"> 3 <span style="color: #93969B">当前状态</span> 4 <span class="font-12"> 5 <input type="checkbox" name="currentStatus" class="e-selfecheckbox" value="" id="currentStatus1" checked> 6 <label for="currentStatus1"> 不限</label> 7 <input type="checkbox" name="currentStatus" class="e-selfecheckbox" value="2" id="currentStatus2"> 8 <label class="selfecheckbox_label" for="currentStatus2"> 空闲</label> 9 <input type="checkbox" name="currentStatus" class="e-selfecheckbox" value="3" id="currentStatus3"> 10 <label class="selfecheckbox_label" for="currentStatus3"> 服务中</label> 11 </span> 12 </p> 13 <p class="driverPlaceAlready"> 14 <span style="color: #93969B">司机占用</span> 15 <span class="font-12"> 16 <input type="checkbox" name="driverPlace2" class="e-selfecheckbox" value="" id="driverPlace4" checked> 17 <label for="driverPlace4"> 不限</label> 18 <input type="checkbox" name="driverPlace2" class="e-selfecheckbox" value="2" id="driverPlace5"> 19 <label class="selfecheckbox_label" for="driverPlace5"> 未占用</label> 20 <input type="checkbox"name="driverPlace2" class="e-selfecheckbox" value="3" id="driverPlace6"> 21 <label class="selfecheckbox_label" for="driverPlace6"> 已占用</label> 22 </span> 23 </p> 24 <p class="startData"> 25 <span style="color: #93969B">出发日期</span> 26 <span class="font-12"> 27 <input type="checkbox" name="startData" class="e-selfecheckbox" value="" id="startData1" checked> 28 <label for="startData1"> 不限</label> 29 <input type="checkbox" name="startData" class="e-selfecheckbox" value="0" id="startData2"> 30 <label class="selfecheckbox_label" for="startData2"> 今天</label> 31 <input type="checkbox" name="startData" class="e-selfecheckbox" value="1" id="startData3"> 32 <label class="selfecheckbox_label" for="startData3"> 明天</label> 33 <input type="checkbox" name="startData" class="e-selfecheckbox" value="2" id="startData4"> 34 <label class="selfecheckbox_label" for="startData4"> 后天</label> 35 <input type="checkbox" name="startData" class="e-selfecheckbox" value="3" id="startData5"> 36 <label class="selfecheckbox_label" for="startData5"> ${threeDaysFromNow}</label> 37 <input type="checkbox" name="startData" class="e-selfecheckbox" value="4" id="startData6"> 38 <label class="selfecheckbox_label" for="startData6"> ${fourDaysFromNow}</label> 39 </span> 40 </p> 41 </div>
2、js 文件
function getParams(type) { var carTagForm = $('#carTagForm').serialize(); if (type != 4){ var driverPlaceReady = $(".driverPlaceReady").find(":checked").map(function(index, el) { return $(el).val(); }).get().join(","); var currentStatus = $(".currentStatus").find(":checked").map(function(index, el) { return $(el).val(); }).get().join(","); var driverPlaceAlready = $(".driverPlaceAlready").find(":checked").map(function(index, el) { return $(el).val(); }).get().join(","); var startData = $(".startData").find(":checked").map(function(index, el) { return $(el).val(); }).get().join(","); carTagForm += '&driverPlaceReady=' + driverPlaceReady; carTagForm += '¤tStatus=' + currentStatus; carTagForm += '&driverPlaceAlready=' + driverPlaceAlready; carTagForm += '&startData=' + startData; carTagForm += '&type=' + type; carTagForm += '&carType='+getCurrentBusinessType(); }else { carTagForm += '&type=' + type; carTagForm += '&carType='+getCurrentBusinessType(); } return "&" + carTagForm; }
三、Mapper 文件
<select id="queryPage" resultType="carDto"> SELECT t.uuid, t.plate_num AS plateNum, t.close_reason AS closeReason, su.user_name AS closedBy, t.closed_on AS closedOn, a.name AS agentName, c.short_name AS companyName, t.brand_name AS brandName, t.brand AS brand, t.status AS status, t.car_color AS carColor, l.level_name AS levelName, t.model, t.car_no, t.seats FROM yy_car t LEFT JOIN yy_car_level l ON t.car_level_uuid = l.uuid LEFT JOIN yy_system_regional_agent a ON t.agent_uuid = a.uuid LEFT JOIN yy_system_company c ON t.company_uuid = c.uuid LEFT JOIN yy_driver d ON d.car_uuid = t.uuid LEFT JOIN yy_system_user su ON t.closed_by = su.uuid <if test="type == 2 or type == 1"> LEFT JOIN yy_order o ON o.car_uuid = t.uuid LEFT JOIN yy_order_detail od ON o.uuid = od.order_uuid </if> <where> <if test="uuid != null">AND t.uuid = #{uuid}</if> <if test="cityUuid != null">AND t.city_uuid = #{cityUuid}</if> <if test="levelUuid != null">AND t.car_level_uuid = #{levelUuid}</if> <if test="mix != null">AND ( t.plate_num like CONCAT(CONCAT('%',#{mix}),'%') OR EXISTS (SELECT 1 FROM yy_driver d WHERE t.uuid = d.car_uuid AND ( d.name like CONCAT(CONCAT('%',#{mix}),'%') OR d.mobile like CONCAT(CONCAT('%',#{mix}),'%')))) </if> <if test="appid != null">AND t.appid = #{appid}</if> <if test="companyUuid != null">AND t.company_uuid = #{companyUuid}</if> <if test="carType != null">AND l.car_type = #{carType}</if> <if test="agentUuid != null">AND t.agent_uuid = #{agentUuid}</if> <if test="operationRemark != null">and t.operation_remark = #{operationRemark}</if> <if test="type == 3">AND t.status = 0</if> <if test="type != 3">AND IFNULL(t.status,1) = 1</if> <if test="driverPlaceReady != null"> AND ( (FIND_IN_SET(2,#{driverPlaceReady}) AND (d.car_uuid IS NULL OR d.car_uuid = '') ) OR (FIND_IN_SET(3,#{driverPlaceReady}) AND d.car_uuid IS NOT NULL AND d.car_uuid != '' ) ) </if> <if test="driverPlaceAlready != null"> AND ( (FIND_IN_SET(2,#{driverPlaceAlready}) AND (d.car_uuid IS NULL OR d.car_uuid = '') ) OR (FIND_IN_SET(3,#{driverPlaceAlready}) AND d.car_uuid IS NOT NULL AND d.car_uuid != '' ) ) </if> <if test="type == 2 and currentStatus != null"> AND ( (FIND_IN_SET(2,#{currentStatus}) AND (o.sub_status <= 200 OR o.sub_status >= 500) ) OR (FIND_IN_SET(3,#{currentStatus}) AND (o.sub_status > 200 AND o.sub_status < 500)) ) </if> <if test="startData != null"> <foreach item="o" collection="startData" open="AND (" close=")" separator="OR"> DATE_FORMAT(od.depar_time,'%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL -#{o} DAY) </foreach> </if> </where> GROUP BY t.uuid <if test="type == 1"> HAVING count(case when o.main_status = 1 OR o.main_status = 2 THEN 1 ELSE null end) = 0 </if> <if test="type == 2"> HAVING count(case when o.main_status = 1 OR o.main_status = 2 THEN 1 ELSE null end) > 0 </if> ORDER BY t.created_on DESC,t.uuid </select>
四、效果
