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); } } 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; }
欢迎大家阅读《Mysql各种表格查询含实例，日报，周报，月报，时间差自动计算》,跪求各位点评，by 搞代码
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：Mysql各种表格查询含实例，日报，周报，月报，时间差自动计算
转载请注明原文链接：Mysql各种表格查询含实例，日报，周报，月报，时间差自动计算
