mysql 查询年龄段，星座以及最近7天生日的sql_sql如何实现会员7天内是否生日?-CSDN博客

本文链接：https://blog.csdn.net/QH_JAVA/article/details/52625974

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<Object[]> 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<Object[]> 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')<'12-31') or  ( DATE_FORMAT(birthday,'%m-%d') >'01-01'  and   DATE_FORMAT(birthday,'%m-%d') <'01-19' ))  and status=? and company=?";
		// # 射手座
	
		String sheshou = "select id,birthday from  hrd_staff where  DATE_FORMAT(birthday,'%m-%d') >'11-23'  and  DATE_FORMAT(birthday,'%m-%d')<'12-21' and status=? and company=?";
		// #天蝎座
	
		String tianxie = "select id,birthday from  hrd_staff where  DATE_FORMAT(birthday,'%m-%d') >'10-24'  and  DATE_FORMAT(birthday,'%m-%d')<'11-22' and status=? and company=?";
		// #天秤座
		
		String tianchegn = "select id,birthday from  hrd_staff where  DATE_FORMAT(birthday,'%m-%d') >'09-23'  and  DATE_FORMAT(birthday,'%m-%d')<'10-23' and status=? and company=?";
		// #处女座
		
		String chunv = "select id,birthday from  hrd_staff where  DATE_FORMAT(birthday,'%m-%d') >'08-23'  and  DATE_FORMAT(birthday,'%m-%d')<'09-22' and status=? and company=?";
		// #狮子座
		
		String shizi = "select id,birthday from  hrd_staff where  DATE_FORMAT(birthday,'%m-%d') >'07-23'  and  DATE_FORMAT(birthday,'%m-%d')<'08-22' and status=? and company=?";
		// #巨蟹座
		
		String juxie = "select id,birthday from  hrd_staff where  DATE_FORMAT(birthday,'%m-%d') >'06-22'  and  DATE_FORMAT(birthday,'%m-%d')<'07-22' and status=? and company=?";
		// #双子座
		
		String shuangzi = "select id,birthday from  hrd_staff where  DATE_FORMAT(birthday,'%m-%d') >'05-21'  and  DATE_FORMAT(birthday,'%m-%d')<'06-21' and status=? and company=?";
		// #金牛座
		
		String jinniu = "select id,birthday from  hrd_staff where  DATE_FORMAT(birthday,'%m-%d') >'04-20'  and  DATE_FORMAT(birthday,'%m-%d')<'05-20' and status=? and company=?";
		// #白羊座
		
		String baiyang = "select id,birthday from  hrd_staff where  DATE_FORMAT(birthday,'%m-%d') >'03-21'  and  DATE_FORMAT(birthday,'%m-%d')<'04-19' and status=? and company=?";
		// #双鱼座
		
		String shaugnyu = "select id,birthday from  hrd_staff where  DATE_FORMAT(birthday,'%m-%d') >'02-19'  and  DATE_FORMAT(birthday,'%m-%d')<'03-20' and status=? and company=?";
		// #水瓶
		
		String shuipin = "select id,birthday from  hrd_staff where  DATE_FORMAT(birthday,'%m-%d') >'01-20'  and  DATE_FORMAT(birthday,'%m-%d')<'02-18' and status=? and company=?";

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<intmm2){
		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