根据时间段,mysql 把日期作为列名.统计 java 拼接sql

import java.text.*;
import java.util.Date;
 
public class Hello {
    String dateFormat = "yyyy-MM-dd";
    SimpleDateFormat format = new SimpleDateFormat(dateFormat);
 
    public static void main(String[] args) {
        Hello t = new Hello();
 
        String date1 = "2018-10-15";
        String date2 = "2018-10-21";
 
        t.process(date1, date2);
    }
 
    private void process(String date1, String date2){
        if(date1.equals(date2)){
            System.out.println("两个日期相等!");
            return;
        }
 
        String tmp;
        String sql1 = "";
        if(date1.compareTo(date2) > 0){  //确保 date1的日期不晚于date2
            tmp = date1; date1 = date2; date2 = tmp;
        }
 
        tmp = format.format(str2Date(date1).getTime() + 3600*24*1000);
 
        int num = 0;
        while(tmp.compareTo(date2) < 0){                   
            
        System.out.println(tmp);    
             
         sql1 = sql1+" SUM(IF(DATE_FORMAT(createtime,"+ "\"%Y-%m-%d\"" +") =" +
        "'"+tmp+"'"+", male+female, 0)) AS " +"'"+tmp+"'"+"," ;
           
            num++;
            tmp = format.format(str2Date(tmp).getTime() + 3600*24*1000);
         
        }
        //
        sql1="SELECT wokstationid, male+female,createtime, " +
        sql1+ " createtime " +
         " FROM t_b_events WHERE createtime > '2018-10-17' AND createtime < '2019-01-01'  AND wokstationid=145";
 
         System.out.println(sql1);
        if(num == 0)
            System.out.println("两个日期相邻!");
    }
 
private Date str2Date(String str) {
        if (str == null) return null;
 
        try {
            return format.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }
}

 

/*

格式:

id    male    2018-10-1      2018-10-2     2018-10-3

1     2           3                         1                  2

2      1           1                        0                 8

*/