MySQL函数 FIND_IN_SET 实现多条件搜索

JMCui

发布于 2022-03-10 15:26:05

1.2K00

代码可运行

文章被收录于专栏：JMCui

运行总次数：0

代码可运行

一、目标

想实现如下去哪儿网的一个多条件搜索功能，就是勾选了上面的条件，下面的内容就根据上面勾选条件自动选择展示......

二、前端

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                     &nbsp;&nbsp;<input type="checkbox" name="currentStatus" class="e-selfecheckbox" value="" id="currentStatus1" checked>
 6                                 <label for="currentStatus1">&nbsp;不限</label>
 7                     &nbsp;&nbsp;<input type="checkbox" name="currentStatus" class="e-selfecheckbox" value="2" id="currentStatus2">
 8                                 <label class="selfecheckbox_label" for="currentStatus2">&nbsp;空闲</label>
 9                     &nbsp;&nbsp;<input type="checkbox" name="currentStatus" class="e-selfecheckbox" value="3" id="currentStatus3">
10                                 <label class="selfecheckbox_label" for="currentStatus3">&nbsp;服务中</label>
11                 </span>
12         </p>
13         <p class="driverPlaceAlready">
14             <span style="color: #93969B">司机占用</span>
15             <span class="font-12">
16                     &nbsp;&nbsp;<input type="checkbox" name="driverPlace2" class="e-selfecheckbox" value="" id="driverPlace4" checked>
17                                 <label for="driverPlace4">&nbsp;不限</label>
18                     &nbsp;&nbsp;<input type="checkbox" name="driverPlace2" class="e-selfecheckbox" value="2" id="driverPlace5">
19                                 <label class="selfecheckbox_label" for="driverPlace5">&nbsp;未占用</label>
20                     &nbsp;&nbsp;<input type="checkbox"name="driverPlace2" class="e-selfecheckbox" value="3" id="driverPlace6">
21                                 <label class="selfecheckbox_label" for="driverPlace6">&nbsp;已占用</label>
22                 </span>
23         </p>
24         <p class="startData">
25             <span style="color: #93969B">出发日期</span>
26             <span class="font-12">
27                     &nbsp;&nbsp;<input type="checkbox" name="startData" class="e-selfecheckbox" value="" id="startData1" checked>
28                                 <label for="startData1">&nbsp;不限</label>
29                     &nbsp;&nbsp;<input type="checkbox" name="startData" class="e-selfecheckbox" value="0" id="startData2">
30                                 <label class="selfecheckbox_label" for="startData2">&nbsp;今天</label>
31                     &nbsp;&nbsp;<input type="checkbox" name="startData" class="e-selfecheckbox" value="1" id="startData3">
32                                 <label class="selfecheckbox_label" for="startData3">&nbsp;明天</label>
33                     &nbsp;&nbsp;<input type="checkbox" name="startData" class="e-selfecheckbox" value="2" id="startData4">
34                                 <label class="selfecheckbox_label" for="startData4">&nbsp;后天</label>
35                     &nbsp;&nbsp;<input type="checkbox" name="startData" class="e-selfecheckbox" value="3" id="startData5">
36                                 <label class="selfecheckbox_label" for="startData5">&nbsp;${threeDaysFromNow}</label>
37                     &nbsp;&nbsp;<input type="checkbox" name="startData" class="e-selfecheckbox" value="4" id="startData6">
38                                 <label class="selfecheckbox_label" for="startData6">&nbsp;${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 += '&currentStatus=' + 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 &lt;= 200 OR o.sub_status >= 500) )
                OR
                (FIND_IN_SET(3,#{currentStatus}) AND (o.sub_status &gt; 200 AND o.sub_status &lt; 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>