开发工具与关键技术： Java
作者：肖广斌
撰写时间：2021年5月24日

在大部分的地方我们都会用到模糊查询的功能，模糊查询的条件也是各种各样的，其中就有年龄段的筛选和时间段的筛选，那下面就来看一下怎么使用这两个条件进行模糊查询。

根据身份证计算年龄关键sql语句:
SELECT (YEAR(NOW())- SUBSTRING(表的身份证字段,7,4)) age FROM 表名;
就是计算当前年份减去从身份证字段里截取到的年份，因为从第七位开始就是年份，截取四位得到年份，然后计算得到年龄，这是18位数身份证的，15位的也大同小异。
1、首先得在mapper层写条件查询的接口方法
写一个传入多个参数作为查询条件最后返回list的方法

//多条件分页查询学员信息
List<Students> selStudents(@Param("nameIdPhone")String nameIdPhone,
                           @Param("departmentID")Integer departmentID,
                           @Param("namePointID")Integer namePointID,
                           @Param("recruitStudentID")Integer recruitStudentID,
                           @Param("driveTypeID")Integer driveTypeID,
                           @Param("shiftClassID")Integer shiftClassID,
                           @Param("sex")Boolean sex,
                           @Param("startTime")String startTime,@Param("endTime")String endTime,
                           @Param("ageID1")Integer ageID1,@Param("ageID2")Integer ageID2);

2、然后就是在xml中写sql语句
连表查询+条件判断。
关键sql语句：
1、表名.字段 between #{开始时间} and #{结束时间}
2、(YEAR(NOW())- SUBSTRING(身份证字段,7,4)) between #{年龄1} and #{年龄2}
between 在xxx和xxx范围之间

<!-- 多条件分页查询学员信息 -->
<select id="selStudents" resultType="com.jx.pojo.Students">
   select students .*,status.StatusName,registrationpoint.RPName,drivingtype.drivingTypeName,
	shiftclass.SCName,recruitstudent.RecruitName,department.DepartmentName,
	studentgroup.SGName from students
          left join status on status.StatusID = students.StatusID
          left join registrationpoint on registrationpoint.RegistrationPointID = students.RegistrationPointID
          left join drivingtype on drivingtype.drivingTypeID = students.drivingTypeID
          left join shiftclass on shiftclass.ShiftClassID = students.ShiftClassID
          left join recruitstudent on recruitstudent.RecruitStudentID = students.RecruitStudentID
          left join department on department.DepartmentID = students.DepartmentID
          left join studentgroup on studentgroup.StudentGroupID = students.StudentGroupID
	<where>
        <if test="nameIdPhone != null and nameIdPhone != ''" >
            and (students.name like concat('%',#{nameIdPhone},'%')
            or students.idNumber like concat('%',#{nameIdPhone},'%')
            or students.phoneOne like concat('%',#{nameIdPhone},'%')
            or students.phoneTwo like concat('%',#{nameIdPhone},'%'))
        </if>
        <if test="departmentID != 0 and departmentID != ''">
            and department.PDepartmentID = #{departmentID}
        </if>
        <if test="namePointID != 0 and namePointID != ''">
            and students.RegistrationPointID = #{namePointID}
        </if>
        <if test="recruitStudentID != 0 and recruitStudentID != ''">
            and students.RecruitStudentID = #{recruitStudentID}
        </if>
        <if test="driveTypeID != 0 and driveTypeID != ''">
            and students.drivingTypeID = #{driveTypeID}
        </if>
        <if test="shiftClassID != 0 and shiftClassID != ''">
            and students.ShiftClassID = #{shiftClassID}
        </if>
        <if test="sex != null">
            and students.sex = #{sex}
        </if>
        <if test="startTime!=null">
            and students.registrationTime between #{startTime} and  #{endTime}
        </if>
        <if test="endTime!=null">
            and students.registrationTime between #{startTime} and  #{endTime}
        </if>
        <if test="ageID1 != 0 and ageID1 != ''" >
            and (YEAR(NOW())- SUBSTRING(idNumber,7,4)) between #{ageID1} and  #{ageID2}
        </if>
        <if test="ageID2 != 0 and ageID2 != ''">
            and (YEAR(NOW())- SUBSTRING(idNumber,7,4)) between #{ageID1} and  #{ageID2}
        </if>
    </where>
</select>

3、接着在jsp页面写条件查询的方法
获取页面参数，加入条件判断之后传到控制器

//条件查询
function CheckSignUp(){
    var nameIdPhone = $("#nameIdPhone").val();
    var namePointID = $("#NamePointID").val();
    var departmentID = $("#DepartmentID").val();
    var recruitStudentID = $("#RecruitStudentID").val();
    var test1 = $("#test1").val();
    var ageID1 = $("#AgeID1").val();
    var ageID2 = $("#AgeID2").val();
    var driveTypeID = $("#DriveTypeID").val();
    var shiftClassID = $("#ShiftClassID").val();
    var sexID = $("#SexID").val();
    var startTime = test1.substring(0, 10);
    var endTime = test1.substring(13);
    if (nameIdPhone == undefined || nameIdPhone == null || nameIdPhone == "") {
        nameIdPhone = "";
    }
    if (departmentID == "" || departmentID == undefined || departmentID == null) {
        departmentID = 0;
    }
    if (namePointID == "" || namePointID == undefined || namePointID == null) {
        namePointID = 0;
    }
    if (recruitStudentID == undefined || recruitStudentID == "" || recruitStudentID == null) {
        recruitStudentID = 0;
    }
    if (driveTypeID == undefined || driveTypeID == "" || driveTypeID == null) {
        driveTypeID = 0;
    }
    if (shiftClassID == undefined || shiftClassID == "" || shiftClassID == null) {
        shiftClassID = 0;
    }
    if(startTime==""||startTime==null||endTime==""||endTime==null){
        startTime="undefined";
        endTime="undefined"
    }else{
        startTime = new Date(startTime).getTime();
        endTime = new Date(endTime).getTime();
    }
    if (ageID1 == undefined || ageID1 == "" || ageID1 == null) {
        ageID1 = 0;
    }
    if (ageID2 == undefined || ageID2 == "" || ageID2 == null) {
        ageID2 = 0;
    }
    //表格数据重载
    //方法级渲染的重载
    tabSignUp.reload({
        url: "${ctx}/Students/selStudents",
        where: {
            nameIdPhone:nameIdPhone,
            departmentID:departmentID,
            namePointID:namePointID,
            recruitStudentID:recruitStudentID,
            driveTypeID:driveTypeID,
            shiftClassID:shiftClassID,
            startTime:startTime,
            endTime:endTime,
            sexID:sexID,
            ageID1:ageID1,
            ageID2:ageID2
        },
        page: {
            curr: 1 //重新从第 1 页开始
        }
    });
}

4、最后在控制器写查询的逻辑
接收页面传递过来的参数，传入查询方法进行数据查询，最后返回data

//条件分页查询学员信息
@ResponseBody
@RequestMapping(value = "/selStudents", produces = "application/json; charset=utf-8")
private Layui selStudents(HttpServletRequest request,String nameIdPhone,Integer departmentID,
                          Integer namePointID,Integer recruitStudentID,Integer driveTypeID,
                          Integer shiftClassID,String startTime,String endTime,Boolean sexID,
                          Integer ageID1,Integer ageID2) {
    //获取参数
    String pageNum = request.getParameter("curr");
    String pageSize = request.getParameter("nums");
    if("".equals(startTime)||startTime==null||"undefined".equals(startTime)) {
        startTime=null;
    }else {
        startTime = new SimpleDateFormat("yyyy-MM-dd").format(Long.parseLong(startTime));
    }
    if("".equals(endTime)||endTime==null||"undefined".equals(endTime)) {
        endTime=null;
    }else {
        endTime = new SimpleDateFormat("yyyy-MM-dd").format(Long.parseLong(endTime));
    }
    List<Students> list = new ArrayList<>();
    //查询数据
    list = StudentsMapper.selStudents(nameIdPhone,departmentID,namePointID,recruitStudentID,
                                       driveTypeID,shiftClassID,sexID,startTime,endTime,ageID1,ageID2);

    //循环学员表信息
    for (Students student : list) {
        //声明变量和空字符串
        String str = "";
        //拼接车型+班制
        str += student.getDrivingtypename()+"-"+student.getScname();
        student.setCarclassname(str);//赋值
    }

    PageService<Students> stu= new PageService<Students>();
    PageBean<Students> pageBean = stu.findByPage(list, Integer.parseInt(pageNum), 	Integer.parseInt(pageSize));
    return Layui.data(pageBean);
}

演示效果如下：

输入查询条件前

mysql查询出生年月在之间的 查询出生年份sql_sql语句


输入查询条件后

mysql查询出生年月在之间的 查询出生年份sql_条件查询_02


还有就是年龄的查询条件不能从大到小，开始年龄比结束年龄大、不能两个年龄相同、不能只写开始年龄不写结束年龄，或者只写结束年龄不写开始年龄，这样都是无法查询数据的。如下图：

mysql查询出生年月在之间的 查询出生年份sql_字段_03


mysql查询出生年月在之间的 查询出生年份sql_mysql查询出生年月在之间的_04


mysql查询出生年月在之间的 查询出生年份sql_sql语句_05


mysql查询出生年月在之间的 查询出生年份sql_sql语句_06