按出生日期统计年龄段oracle原生SQL和hibernate中查询设计
select agerange ,count(*) as count from( select case when to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy') >= '0' and to_char (sysdate,'yyyy')-to_char(birthdate,'yyyy') < '20' then '20岁以下' when to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy') >= '20' and to_char (sysdate,'yyyy')-to_char(birthdate,'yyyy')<='30' then '20-30' when to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy') >= '30' and to_char (sysdate,'yyyy')-to_char(birthdate,'yyyy')<='40' then '30-40' when to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy') >= '40' and to_char (sysdate,'yyyy')-to_char(birthdate,'yyyy')<='50' then '40-50' when to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy') >= '50' and to_char (sysdate,'yyyy')-to_char(birthdate,'yyyy')<='60' then '50-60' when to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy') >'60' then '60岁以上' end as agerange from hr_resume)group by agerange
??? 根据数据库表中的出生日期统计出各个年龄段的人数，在oracle中测试通过
?
?
?
hibernate HQL函数
String hql1 =" select agerange ,count(agerange) as count from "+ "(select case "+ " when year(current_date())-year(r.birthdate) >= 0 and year(current_date())-year(r.birthdate) < 20 then '20岁以下' "+ " when year(current_date())-year(r.birthdate) >= 20 and year(current_date())-year(r.birthdate)<= 30 then '20-30' "+ " when year(current_date())-year(r.birthdate) >= 30 and year(current_date())-year(r.birthdate)<= 40 then '30-40' "+ " when year(current_date())-year(r.birthdate) >= 40 and year(current_date())-year(r.birthdate)<= 50 then '40-50' "+ " when year(current_date())-year(r.birthdate) >= 50 and year(current_date())-year(r.birthdate)<= 60 then '50-60' "+ " when year(current_date())-year(r.birthdate) > 60 then '60岁以上' end as agerange from Resume r"+ " )group by agerange";?错误二HQL写法
String hql2= "select " + " sum(case when year(current_date())-year(r.r.birthdate) >= 0 and year(current_date())-year(r.birthdate) < 20 then 1 else 0 end),"+ " sum(case when year(current_date())-year(r.birthdate) >= 20 and year(current_date())-year(r.birthdate)<= 30 then 1 else 0 end),"+ " sum(case when year(current_date())-year(r.birthdate) >= 30 and year(current_date())-year(r.birthdate)<= 40 then 1 else 0 end),"+ " sum(case when year(current_date())-year(r.birthdate) >= 40 and year(current_date())-year(r.birthdate)<= 50 then 1 else 0 end),"+ " sum(case when year(current_date())-year(r.birthdate) >= 50 and year(current_date())-year(r.birthdate)<= 60 then 1 else 0 end),"+ " from Resume r where 1=1 group by r.birthdate ";
?正确的NativeSql写法
?
String nativeSQL="select agerange ,count(*) as count from"+"(select case"+" when to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy') >= '0' and to_char (sysdate,'yyyy')-to_char(birthdate,'yyyy') < '20' then '20岁以下' "+" when to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy') >= '20' and to_char (sysdate,'yyyy')-to_char(birthdate,'yyyy')< '30' then '20岁~29岁' "+" when to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy') >= '30' and to_char (sysdate,'yyyy')-to_char(birthdate,'yyyy')< '40' then '30岁~39岁' "+" when to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy') >= '40' and to_char (sysdate,'yyyy')-to_char(birthdate,'yyyy')< '50' then '40岁~50岁' "+" when to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy') >= '50' and to_char (sysdate,'yyyy')-to_char(birthdate,'yyyy')<='60' then '50岁~60岁' "+" when to_char(sysdate,'yyyy')-to_char(birthdate,'yyyy') >'60' then '60岁以上' end as agerange from hr_resume "+") group by agerange "; List<Object[]> list = this.getSession().createSQLQuery(nativeSQL).list(); return list;?
