年周转换成x月第几周

sql语句查询的是按照周统计，但是需求要显示某月的第几周

如下的介绍mysql统计查询的方法

按天统计
select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days;  
按月统计
select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months; 
DATE_FORMAT(date,format) 
按天统计
select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;  
或 查看MySQL的manual
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
其中
1如果周一为一周的第一天,则(小写) DATE_FORMAT(date,'%x %v')
2如果周日为一周的第一天,则(大写) DATE_FORMAT(date,'%X %V')
select DATE_FORMAT(date,'%x年-第%v周') as week,sum(money) as money from finance_base where DATE_FORMAT(date,'%Y')=2010 group by week
select DATE_FORMAT(date,'%x年-第%v周') as week,sum(money) as money from finance_base where DATE_FORMAT(date,'%Y')=2010 group by week

// 2022-15  2022年第15周
    public static String getMonthOfWeekNoByYearWeekNo(String yearWeekNo){
        int year = Integer.valueOf(yearWeekNo.split("-")[0]);
        int weekNo = Integer.valueOf(yearWeekNo.split("-")[1]);
        Calendar cal =Calendar.getInstance();
        cal.set(Calendar.DAY_OF_WEEK,Calendar.MONDAY);
        cal.set(Calendar.YEAR, year);
        cal.set(Calendar.WEEK_OF_YEAR, weekNo);
        // 获取了这周的开始时间
        String dateTime = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-"+
                cal.get(Calendar.DAY_OF_MONTH);
        //System.out.println("开始时间：" + dateTime);
        return (cal.get(Calendar.MONTH)+1)+"月第"+cal.get(Calendar.WEEK_OF_MONTH)+"周";

    }


    public static void main(String[] args) {

        System.out.println("转换结果为：" +getMonthOfWeekNoByYearWeekNo("2022-27"));
    }

输出结果为：  6月第5周

 

 如下方法的得到当前时间的上几周或者下几周的周一或者周日时间

//根据所传参数，计算前几周或者后几周的周一和周日的日期
    //传正数获取未来周一和周日的日期，传负数获取过去周一和周日的日期
    public static String getPreOrNextMonday(int num){
        SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar1 = Calendar.getInstance();
        Calendar calendar2 = Calendar.getInstance();
        int dayOfWeek=calendar1.get(Calendar.DAY_OF_WEEK)-1;
        int offset1=1-dayOfWeek;
        int offset2=7-dayOfWeek;
        calendar1.add(Calendar.DATE, offset1+7*num);
        calendar2.add(Calendar.DATE, offset2+7*num);
        Date timePreMonday= calendar1.getTime();
        Date timeThisMonday = calendar2.getTime();
        String preMonday = simpleDateFormat.format(timePreMonday);//获取符合要求格式的上周周一日期
        //String nextSunday = simpleDateFormat.format(timeThisMonday);//获取符合要求格式的上周周日的日期
        //return preMonday+","+nextSunday;
        return preMonday;
    }