Mysql各种报表查询含实例，日报，周报，月报，时间差自动计算

lovegigi

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);
}
}
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";
} 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;
}