Sql根据⾝份证计算年龄和时间范围的条件查询
开发⼯具与关键技术： Java
作者：肖⼴斌
撰写时间：2021年5⽉24⽇
在⼤部分的地⽅我们都会⽤到模糊查询的功能，模糊查询的条件也是各种各样的，其中就有年龄段的筛选和时间段的筛选，那下⾯就来看⼀下怎么使⽤这两个条件进⾏模糊查询。
根据⾝份证计算年龄关键sql语句:
SELECT (YEAR(NOW())- SUBSTRING(表的⾝份证字段,7,4)) age FROM 表名;
就是计算当前年份减去从⾝份证字段⾥截取到的年份，因为从第七位开始就是年份，截取四位得到年份，然后计算得到年龄，这是18位数⾝份证的，15位的也⼤同⼩异。
1、⾸先得在mapper层写条件查询的接⼝⽅法
写⼀个传⼊多个参数作为查询条件最后返回list的⽅法
//多条件分页查询学员信息
List<Students>lStudents(@Param("nameIdPhone")String nameIdPhone,
@Param("departmentID")Integer departmentID,
@Param("namePointID")Integer namePointID,
@Param("recruitStudentID")Integer recruitStudentID,
@Param("driveTypeID")Integer driveTypeID,aone
@Param("shiftClassID")Integer shiftClassID,
@Param("x")Boolean x,
@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范围之间
<!--多条件分页查询学员信息-->
<lect id="lStudents" resultType="com.jx.pojo.Students">
lect 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="x != null">
没什么大不了的英文
and students.x = #{x}
</if>
<if test="startTime!=null">
istrationTime between #{startTime} and #{endTime}
</if>
<if test="endTime!=null">
istrationTime 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>
</lect>
3、接着在jsp页⾯写条件查询的⽅法shmily意思
获取页⾯参数，加⼊条件判断之后传到控制器
/
/条件查询
function CheckSignUp(){
var nameIdPhone = $("#nameIdPhone").val();
var namePointID = $("#NamePointID").val();
防守英文var departmentID = $("#DepartmentID").val();
dovetailvar recruitStudentID = $("#RecruitStudentID").val();
var test1 = $("#test1").val();
var ageID1 = $("#AgeID1").val();
var ageID2 = $("#AgeID2").val();
var driveTypeID = $("#DriveTypeID").val();
var driveTypeID = $("#DriveTypeID").val();
var shiftClassID = $("#ShiftClassID").val();
var xID = $("#SexID").val();
var startTime = test1.substring(0,10);
var endTime = test1.substring(13);
if(nameIdPhone == undefined || nameIdPhone == null || nameIdPhone ==""){
nameIdPhone ="";pink是啥意思
}
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;
}
mergecellsif(shiftClassID == undefined || shiftClassID ==""|| shiftClassID == null){
shiftClassID =0;
}
if(startTime==""||startTime==null||endTime==""||endTime==null){
startTime="undefined";
endTime="undefined"
}el{
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;
}
//表格数据重载
//⽅法级渲染的重载
url:"${ctx}/Students/lStudents",
where:{era
nameIdPhone:nameIdPhone,
departmentID:departmentID,
namePointID:namePointID,
recruitStudentID:recruitStudentID,
driveTypeID:driveTypeID,
shiftClassID:shiftClassID,
startTime:startTime,
endTime:endTime,
xID:xID,
ageID1:ageID1,
ageID2:ageID2
},
page:{张江集团学校
curr:1//重新从第 1 页开始
}
});
}
4、最后在控制器写查询的逻辑
接收页⾯传递过来的参数，传⼊查询⽅法进⾏数据查询，最后返回data
//条件分页查询学员信息
@ResponBody
@RequestMapping(value ="/lStudents", produces ="application/json; chart=utf-8")
private Layui lStudents(HttpServletRequest request,String nameIdPhone,Integer departmentID,
Integer namePointID,Integer recruitStudentID,Integer driveTypeID,
Integer shiftClassID,String startTime,String endTime,Boolean xID,
Integer ageID1,Integer ageID2){
//获取参数
String pageNum = Parameter("curr");
String pageSize = Parameter("nums");
if("".equals(startTime)||startTime==null||"undefined".equals(startTime)){
startTime=null;
}el{
startTime =new SimpleDateFormat("yyyy-MM-dd").format(Long.parLong(startTime));
}
if("".equals(endTime)||endTime==null||"undefined".equals(endTime)){
endTime=null;
}el{
endTime =new SimpleDateFormat("yyyy-MM-dd").format(Long.parLong(endTime));
}
List<Students> list =new ArrayList<>();
//查询数据
list = StudentsMapper.lStudents(nameIdPhone,departmentID,namePointID,recruitStudentID,
driveTypeID,shiftClassID,xID,startTime,endTime,ageID1,ageID2);
//循环学员表信息
for(Students student : list){
//声明变量和空字符串
String str ="";
//拼接车型+班制
str += Drivingtypename()+"-"+Scname();
student.tCarclassname(str);//赋值
}
PageService<Students> stu=new PageService<Students>();
PageBean<Students> pageBean = stu.findByPage(list, Integer.parInt(pageNum), Integer.parInt(pageSize)); return Layui.data(pageBean);
}vesl是什么意思
演⽰效果如下：
输⼊查询条件前
输⼊查询条件后
还有就是年龄的查询条件不能从⼤到⼩，开始年龄⽐结束年龄⼤、不能两个年龄相同、不能只写开始年龄不写结束年龄，或者只写结束年龄不写开始年龄，这样都是⽆法查询数据的。如下图：
