Mysql报表查询实例(日报|周报|月报|时间差自动计算)

public List<IReport> retrieve(IReport report) { 
List<IReport> list = new ArrayList<IReport>(); 
Map<String, Object> map = new HashMap<String, Object>(); 
 
// 设置结束日期 
if (null != report.getEndTime()) { 
    map.put(ReportMeta.PROP_ENDTIME, report.getEndTime()); 
 
// 设置名称 
if (null != report.getName()) { 
map.put(ReportMeta.PROP_NAME, report.getName()); 
 
// 设置问题分类子项（必须有问题分类名称） 
if (null != report.getQid() && report.getQid() > 0) { 
    map.put(ReportMeta.PROP_QID, report.getQid()); 
} 
} 
 
// 设置查询几周的数据，默认为本周，查询上周设置为：1L，依次类推 
if (null == report.getQid()) { 
report.setQid(0L); 
    } 
} 
  www.jb200.com
String sql = ""; 
// 设置正负面的值 
String tendency = "'正面','中性','负面-中','负面-高','负面-低'"; 
String hourarea = " " + dayhour + ":00:00"; 
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd" + hourarea); 
if (map.size() > 0) { 
// 日报数据查询 
if ("daily".equals(report.getType())) { 
sql = "select name,quantity,start_time startTime,end_time endTime from tbl_report " 
+ "where end_time between date_add(:endTime,interval :dayHour hour_second) and :endTime"; 
 
// 日报正负面数据统计 
    } else if ("daily_tendency".equals(report.getType())) { 
sql = "select name,quantity,start_time startTime,end_time endTime from tbl_report " 
+ "where end_time between date_add(:endTime,interval :dayHour hour_second) " 
+ "and :endTime and name in (:tendency)"; 
 
// 日报问题分类数据统计 
    } else if ("daily_question".equals(report.getType())) { 
sql = "select name,quantity,start_time startTime,end_time endTime from tbl_report " 
+ "where end_time between date_add(:endTime,interval :dayHour hour_second) and :endTime " 
+ "and name in (select content from tbl_keyword where type = '问题分类' and " 
+ "state = 'normal') order by quantity desc"; 
 
// 日报问题分类子类数据统计 
    } else if ("daily_question_child".equals(report.getType())) { 
sql = "select name,quantity,start_time startTime,end_time endTime,qid,description,description2 from tbl_report " 
+ "where end_time between date_add(:endTime,interval :dayHour hour_second) and :endTime " 
+ "and name in (select content from tbl_keyword where type = '问题分类子类' and " 
+ "state = 'normal') "; 
 
// 日报数据查询(周一：上周五15:00~本周一15:00) 
    } else if ("daily_monday".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity from tbl_report " 
+ "where end_time between ADDDATE(:endTime,-WEEKDAY(:endTime)-2) " 
+ "and ADDDATE(:endTime,-WEEKDAY(:endTime)) group by name"; 
 
// 日报正负面数据统计(周一：上周五15:00~本周一15:00) 
    } else if ("daily_tendency_monday".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity from tbl_report " 
+ "where end_time between ADDDATE(:endTime,-WEEKDAY(:endTime)-2) " 
+ "and ADDDATE(:endTime,-WEEKDAY(:endTime)) and name in (:tendency) group by name"; 
 
// 日报问题分类数据统计(周一：上周五15:00~本周一15:00) 
    } else if ("daily_question_monday".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity from tbl_report " 
+ "where end_time between ADDDATE(:endTime,-WEEKDAY(:endTime)-2) " 
+ "and ADDDATE(:endTime,-WEEKDAY(:endTime)) " 
+ "and name in (select content from tbl_keyword where type = '问题分类' and " 
+ "state = 'normal') group by name order by quantity desc"; 
 
// 日报问题分类子类数据统计(周一：上周五15:00~本周一15:00) 
    } else if ("daily_question_child_monday".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity,start_time startTime,end_time endTime,qid,description,description2 from tbl_report " 
+ "where end_time between ADDDATE(:endTime,-WEEKDAY(:endTime)-2) " 
+ "and ADDDATE(:endTime,-WEEKDAY(:endTime)) " 
+ "and name in (select content from tbl_keyword where type = '问题分类子类' and " 
+ "state = 'normal') group by name"; 
 
// 周报每天数据 
    } else if ("weekly_day".equals(report.getType())) { 
sql = "select name,quantity,start_time startTime,end_time endTime from tbl_report " 
+ "where end_time between " 
+ "(select date_sub(:endTime, interval weekday(:endTime) +:plusDay day)) " 
+ "and (select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) " 
+ "order by end_time"; 
 
// 周报所有数据汇总 
    } else if ("weekly_count".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity,start_time startTime,end_time endTime from tbl_report where end_time between " 
+ "(select date_sub(:endTime,interval weekday(:endTime) +:plusDay day)) and " 
+ "(select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) group by name"; 
 
// 周报正负面数据汇总 
    } else if ("weekly_count_tendency".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity from tbl_report where end_time between " 
+ "(select date_sub(:endTime,interval weekday(:endTime) +:plusDay day)) and " 
+ "(select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) and " 
+ "name in (:tendency) group by name"; 
 
// 周报每天有效数据汇总(report.name="今日有效数据") 
    } else if ("weekly_count_valid".equals(report.getType())) { 
// sql = 
// "select '有效数据' name,sum(quantity) quantity,end_time endTime from tbl_report " 
// + 
// "where end_time between (select date_sub(:endTime,interval weekday(:endTime) +:plusDay day)) " 
// + 
// "and (select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) " 
// + 
// "and name in (:tendency) group by end_time order by end_time"; 
sql = "select name,quantity,start_time startTime,end_time endTime from tbl_report where end_time between " 
+ "(select date_sub(:endTime,interval weekday(:endTime) +:plusDay day)) " 
+ "and (select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) " 
+ "and name = :name group by endTime"; 
 
// 周报问题分类数据汇总 
    } else if ("weekly_count_question".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity,start_time startTime,end_time endTime from tbl_report where end_time between " 
+ "(select date_sub(:endTime,interval weekday(:endTime) +:plusDay day)) and " 
+ "(select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) and " 
+ "name in (select content from tbl_keyword where type = '问题分类' and " 
+ "state = 'normal') group by name"; 
 
// 周报问题分类子类数据汇总 
    } else if ("weekly_count_question_child".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity,qid,description,description2 from tbl_report where end_time between " 
+ "(select date_sub(:endTime,interval weekday(:endTime) +:plusDay day)) and " 
+ "(select date_sub(:endTime,interval weekday(:endTime) +:reduceDay day)) and " 
+ "name in (select content from tbl_keyword where type = '问题分类子类' and " 
+ "state = 'normal') group by name"; 
 
// 月报所用数据汇总 
    } else if ("monthly_count".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity from tbl_report where end_time between " 
+ "(select date_add(date_add(last_day(:endTime),interval -1 month)," 
+ "interval +15 hour)) and (select date_add(last_day(:endTime)," 
+ "interval +15 hour)) group by name"; 
 
// 月报正负面数据汇总 
    } else if ("monthly_count_tendency".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity from tbl_report where end_time between " 
+ "(select date_add(date_add(last_day(:endTime),interval -1 month)," 
+ "interval +15 hour)) and (select date_add(last_day(:endTime)," 
+ "interval +15 hour)) and name in (:tendency) group by name"; 
 
// 月报问题分类数据汇总 
    } else if ("monthly_count_question".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity from tbl_report where end_time between " 
+ "(select date_add(date_add(last_day(:endTime),interval -1 month)," 
+ "interval +15 hour)) and (select date_add(last_day(:endTime)," 
+ "interval +15 hour)) and name in (select content from tbl_keyword where type = '问题分类' and " 
+ "state = 'normal') group by name"; 
 
// 月报问题分类子类数据汇总 
    } else if ("monthly_count_question_child".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity,qid,description,description2,description3 from tbl_report where end_time between " 
+ "(select date_add(date_add(last_day(:endTime),interval -1 month)," 
+ "interval +15 hour)) and (select date_add(last_day(:endTime)," 
+ "interval +15 hour)) and name in (select content from tbl_keyword where type = '问题分类子类' and " 
+ "state = 'normal') group by name"; 
  www.jb200.com
    } else if ("monthly_count_valid".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity,start_time startTime,end_time endTime from tbl_report where end_time between " 
+ "(select date_add(date_add(last_day(:endTime),interval -1 month)," 
+ "interval +15 hour)) and (select date_add(last_day(:endTime)," 
+ "interval +15 hour)) " 
+ "and name = :name"; 
// 时间段查询正负面数据汇总 
    } else if ("duration_count_tendency".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity from tbl_report where end_time between " 
+ ":startTime and :endTime and name in (:tendency) group by name"; 
 
// 时间段查询问题分类数据汇总 
    } else if ("duration_count_question".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity,start_time startTime,end_time endTime from tbl_report where end_time between " 
+ ":startTime and :endTime  and " 
+ "name in (select content from tbl_keyword where type = '问题分类' and " 
+ "state = 'normal') group by name"; 
 
// 时间段查询问题分类子类数据汇总 
    } else if ("duration_count_question_child".equals(report.getType())) { 
sql = "select name,sum(quantity) quantity,qid,description,description2 from tbl_report where end_time between " 
+ ":startTime and :endTime  and " 
+ "name in (select content from tbl_keyword where type = '问题分类子类' and " 
+ "state = 'normal') group by name"; 
 
// 日报是否存在 
    } else if ("is_exists".equals(report.getType())) { 
sql = "select name from tbl_report where end_time = :endTime"; 
    } 
 
    sql = sql.replace( 
    ":startTime", 
    "'" 
    + (null != report.getStartTime() ? sdf 
    .format(report.getStartTime()) : "") + "'") 
    .replace(":endTime", 
    "'" + sdf.format(report.getEndTime()) + "'") 
    .replace(":dayHour", "'-23:59:59'").replace(":plusDay", 
    2 + (7 * report.getQid()) + "").replace( 
    ":reduceDay", -4 + (7 * report.getQid()) + "") 
    .replace(":tendency", tendency).replace( 
    ":name", 
    "'" 
    + (null != report.getName() ? report 
    .getName() : "") + "'"); 
    list = reportDao 
    .listQuery(sql, map, QueryStrType.SQL, Report.class); 
} 
 
return list; 
}