Sql根据身份证计算年龄和时间范围的条件查询

喜欢ヅ旅行 2022-10-15 09:53 349阅读 0赞

开发工具与关键技术： 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);
    }

演示效果如下：  
输入查询条件前  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NTA1Nzk3_size_16_color_FFFFFF_t_70]  
输入查询条件后  
![在这里插入图片描述][20210524154241445.png]  
还有就是年龄的查询条件不能从大到小，开始年龄比结束年龄大、不能两个年龄相同、不能只写开始年龄不写结束年龄，或者只写结束年龄不写开始年龄，这样都是无法查询数据的。如下图：  
![在这里插入图片描述][20210524154254346.png]  
![在这里插入图片描述][20210524154300899.png]  
![在这里插入图片描述][20210524154307476.png]  
![在这里插入图片描述][20210524154314556.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NTA1Nzk3_size_16_color_FFFFFF_t_70]: /images/20221014/697a22c11ec84900bff7645ec7f84474.png
[20210524154241445.png]: /images/20221014/7c1c18ff08c24c07ac21cf0b0c1fb9a0.png
[20210524154254346.png]: /images/20221014/02860b4bd0cc4ce782f7e17e13c923b0.png
[20210524154300899.png]: /images/20221014/cf2ef897249144259d79a012443bc53d.png
[20210524154307476.png]: /images/20221014/ce37ad320752457485d95d994a025db7.png
[20210524154314556.png]: /images/20221014/85ad7bb138ff474e9ce939a1b99a9eb8.png