mysql 星座_mysql 查询年龄段，星座以及最近7天生日的sql

1、查询年龄段

小于20

//查询小于20岁的人的个数，DATE_FORMAT(birthday, '%Y')其中的birthday就是数据库的生日这个date类型字段

String hql20="SELECT id, birthday,DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d')) AS age from hrd_staff where status=? and company=? group by id having age< ?";

List list20=staffService.findBySql(hql20, status,company,20);

大于等于20 小于25

//25

String hql30="SELECT id, birthday,DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d')) AS age from hrd_staff where status=? and company=? group by id having age >=? and age";

List list30=staffService.findBySql(hql30, status,company,20,25);

2、查询星座

( DATE_FORMAT(birthday,'%m-%d') 其中的birthday就是数据库中的生日字段

// #摩羯座

String moxie = "select id,birthday from hrd_staff where ( ( DATE_FORMAT(birthday,'%m-%d')>'12-22' and DATE_FORMAT(birthday,'%m-%d')'01-01' and DATE_FORMAT(birthday,'%m-%d')

// # 射手座

String sheshou = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'11-23' and DATE_FORMAT(birthday,'%m-%d')

// #天蝎座

String tianxie = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'10-24' and DATE_FORMAT(birthday,'%m-%d')

// #天秤座

String tianchegn = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'09-23' and DATE_FORMAT(birthday,'%m-%d')

// #处女座

String chunv = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'08-23' and DATE_FORMAT(birthday,'%m-%d')

// #狮子座

String shizi = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'07-23' and DATE_FORMAT(birthday,'%m-%d')

// #巨蟹座

String juxie = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'06-22' and DATE_FORMAT(birthday,'%m-%d')

// #双子座

String shuangzi = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'05-21' and DATE_FORMAT(birthday,'%m-%d')

// #金牛座

String jinniu = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'04-20' and DATE_FORMAT(birthday,'%m-%d')

// #白羊座

String baiyang = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'03-21' and DATE_FORMAT(birthday,'%m-%d')

// #双鱼座

String shaugnyu = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'02-19' and DATE_FORMAT(birthday,'%m-%d')

// #水瓶

String shuipin = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'01-20' and DATE_FORMAT(birthday,'%m-%d')

3、最近7天生日的sql

int bwt=7;

//现在日期

String nowdate=DateUtil.getNow();

// 截取月日

String wnowdate=nowdate.substring(5, 10);

//获取7天后的日期

String after=DateUtil.getAfterDay(new Date(), bwt);

//截取月日

String wafter=after.substring(5, 10);

//截取月 判断是否是跨年，如果跨年则月份有可能会小于之后几天的 就比如 12.28 七天后就是01.04 这样就是月份很小了

String ny=wnowdate.substring(0,2);

String ay=wafter.substring(0,2);

int iny=Integer.valueOf(ny);

int iay=Integer.valueOf(ay);

//跨年查询

String y12="12-31";

String y1="01-01";

// 跨年查询，因为是生日，之和月份和日期有关和年没关系

if(iny>iay){

//跨年查询sql

hql+=" and ((DATE_FORMAT( birthday , '%m-%d') > '"+wnowdate+"' and DATE_FORMAT( birthday , '%m-%d') <= '"+y12+"') or (DATE_FORMAT( birthday , '%m-%d') >= '"+y1+"' and DATE_FORMAT( birthday , '%m-%d') < '"+wafter+"'))";

}else{

//正常查询sql

hql+=" and DATE_FORMAT( birthday , '%m-%d') > '"+wnowdate+"' and DATE_FORMAT( birthday , '%m-%d') < '"+wafter+"'";

}

4、获取两个日期间的天数

/**

* 两个时间参数，月 日之间的天数

* @param min

* @param max

* @return

*/

public static int days(Date min,Date max){

SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd");

//获取日期

String s1=sdf.format(min);

String s2=sdf.format(max);

//截取月份

String mm1=s1.substring(5, 7);

String mm2=s2.substring(5, 7);

//截取日期

String dd1=s1.substring(8, 10);

String dd2=s2.substring(8, 10);

//月份

int intmm1=Integer.valueOf(mm1);

int intmm2=Integer.valueOf(mm2);

//日期

int intdd1=Integer.valueOf(dd1);

int intdd2=Integer.valueOf(dd2);

int days=0;

// 吧下面的 DAY_OF_YEAR 改为 DAY_OF_MONTH 因为每年的日期不同所以不同年的同一个月的时间不一样

if(intmm1

Calendar aCalendar = Calendar.getInstance();

aCalendar.setTime(min);

int day1 = aCalendar.get(Calendar.DAY_OF_YEAR);

aCalendar.setTime(max);

int day2 = aCalendar.get(Calendar.DAY_OF_YEAR);

days= day2-day1;

}else if(intmm1==intmm2){

if(intdd2>intdd1){

Calendar aCalendar = Calendar.getInstance();

aCalendar.setTime(min);

int day1 = aCalendar.get(Calendar.DAY_OF_MONTH);

aCalendar.setTime(max);

int day2 = aCalendar.get(Calendar.DAY_OF_MONTH);

days= day2-day1;

}else{

Calendar aCalendar = Calendar.getInstance();

aCalendar.setTime(max);

int day1 = aCalendar.get(Calendar.DAY_OF_MONTH);

aCalendar.setTime(min);

int day2 = aCalendar.get(Calendar.DAY_OF_MONTH);

days= day2-day1;

}

}else{

Calendar aCalendar = Calendar.getInstance();

aCalendar.setTime(max);

int day1 = aCalendar.get(Calendar.DAY_OF_YEAR);

aCalendar.setTime(min);

int day2 = aCalendar.get(Calendar.DAY_OF_YEAR);

days= day2-day1;

}

//System.out.println("s1:"+s1 +" s2:"+s2 +" days:"+days);

return days;

} 这个时间就只能这样笨办法写了在这里也看到了一篇文章：https://my.oschina.net/u/1454838/blog/750356