Sql根据⾝份证计算年龄和时间范围的条件查询
开发⼯具与关键技术： 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">
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>
</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 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;
}
//表格数据重载
//⽅法级渲染的重载
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:{
param namecurr: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 = Parameter("curr");
String pageSize = Parameter("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 += Drivingtypename()+"-"+Scname();
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);
}
演⽰效果如下：
输⼊查询条件前
输⼊查询条件后
还有就是年龄的查询条件不能从⼤到⼩，开始年龄⽐结束年龄⼤、不能两个年龄相同、不能只写开始年龄不写结束年龄，或者只写结束年龄不写开始年龄，这样都是⽆法查询数据的。如下图：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论