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

评论

1 楼 123qqwe 2016-06-22  

你确定,能被看懂!!!!