完整的可按年份和月份查询数据并显示

效果:

页面的数据准备:

		Map<String, String> map = findresult(type, date);
		List<StatisticsInterrogate> list = statisticsService.StatisticsInterrogatelist(map);
		request.setAttribute("StatisticsResult", list);

这里的JavaBean:

public class StatisticsInterrogate implements Serializable{
	
	private static final long serialVersionUID = 8738708908641883840L;
	/** 用户ID */
	private Long id;
	/** 用户姓名*/
	private String name;
	/** 提问数*/
	private int sumQuestion;
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getSumQuestion() {
		return sumQuestion;
	}
	public void setSumQuestion(int sumQuestion) {
		this.sumQuestion = sumQuestion;
	}
	
}

sql对应的.xml

<select id="StatisticsInterrogatelist" resultMap="StatisticsInterrogate"
		parameterType="map">
		<![CDATA[
			select u,t.name,count from(select userid u,count(userid) count from T_QUESTION t where
		]]>

		<if test="type =='month'">
			<![CDATA[
				to_char(createtime,'YYYYMM') = #{date} 
			]]>
		</if>
		<if test="type =='year'">
			<![CDATA[
				to_char(createtime,'YYYY') = #{date} 
			]]>
		</if>
		
		<![CDATA[
			group by userid  ) left join(select id,name from T_USER) t on u = t.id 
			where rownum<=20 order by count desc
		]]>
	</select>

只是个参考,还是javabean有用,findresult:

public Map<String, String> findresult(String type, String date) {//返回的date是YYYY或YYYYMM
		if (type == null) {
			type = "month";
			Calendar c = Calendar.getInstance();
			int y = c.get(Calendar.YEAR);
			int m = c.get(Calendar.MONTH) + 1;
			if (m == 1) {
				m = 12;
				y -= 1;
			}
			if (m < 10) {
				date = y + "0" + m;
			} else {
				date = y + "" + m;
			}
		}

		request.setAttribute("yearfind", date.subSequence(0, 4));
		if (type.equals("month")) {
			CharSequence c = date.subSequence(4, 5);
			if (c.equals("0")) {
				request.setAttribute("monthfind", date.subSequence(5, 6) + "月");
				request.setAttribute("monthfind2", date.subSequence(5, 6));
			} else {
				request.setAttribute("monthfind", date.subSequence(4, 6) + "月");
				request.setAttribute("monthfind2", date.subSequence(4, 6));
			}
		}

		Map<String, String> map = new HashMap<String, String>();
		map.put("date", date);
		map.put("type", type);

		return map;
	}

然后jsp,用到了Bootstrap和jquery,

<style type="text/css">
.center {
	width: auto;
	display: table;
	margin-left: auto;
	margin-right: auto;
}

.width40 {
	width: 40%;
}
</style>

<table class="table">
		<tr>
			<td class="width40">
				<table class="table">
					<tr>
						<td class="text-center"><h3>查询条件</h3></td>
					</tr>
					<tr>
						<td>
							<div class="radio">
								<label> <input type="radio" name="datetype" id="dtmonth"
									value="month" οnclick="choosemonth()" checked="checked">
									按月查询
								</label>
							</div>
							<div class="radio">
								<label> <input type="radio" name="datetype" id="dtyear"
									value="year" οnclick="chooseyear()"> 按年查询
								</label>
							</div>
						</td>
					</tr>
				</table>

				<table class="table">
					<tr>
						<td>
							<div>
							<select class="form-control" id="year">
							<c:forEach begin="2014" end="${currentYear }"                 <span style="font-family: Arial, Helvetica, sans-serif;">${currentYear }传来的今年年份</span>
								varStatus="status" var="year">
								<option value="${currentYear - (year - 2014)}">${currentYear - (year - 2014)}年</option>
							</c:forEach>
							</select>
							</div>
						</td>
					</tr>
					<tr>
						<td><select class="form-control " id="month">
								<option value="1">1月</option>
								<option value="2">2月</option>
								<option value="3">3月</option>
								<option value="4">4月</option>
								<option value="5">5月</option>
								<option value="6">6月</option>
								<option value="7">7月</option>
								<option value="8">8月</option>
								<option value="9">9月</option>
								<option value="10">10月</option>
								<option value="11">11月</option>
								<option value="12">12月</option>
						</select></td>

					</tr>
					<tr>
						<td><button οnclick="find()" class="btn btn-info">查询</button></td>
					</tr>
				</table>

			</td>
			<td>
				<table class="table table-striped table-bordered">
					<tr>
						<td colspan="3" class="text-center"><h4>
								<a style="color: blue">${yearfind }年${monthfind }</a>的提出问题排名
							</h4></td>
					</tr>
					<tr>
						<td class="text-center">排名</td>
						<td class="text-center">姓名</td>
						<td class="text-center">提问数</td>
					</tr>
					<c:forEach items="${StatisticsResult }" var="interrogate"
						varStatus="stauts">
						<tr>
							<td class="text-center">${stauts.index+1 }</td>
							<td class="text-center">${interrogate.name}</td>
							<td class="text-center">${interrogate.sumQuestion }</td>
						</tr>
					</c:forEach>

				</table>

			</td>
		</tr>
	</table>

js:

$(function() {//第一次载入是上个月,之后是自己选的
		$('#year').val('${yearfind }');
		var m = '${monthfind2 }';

		if (m != '') {
			$('#month').val(m);
		} else {
			chooseyear();
			var selects = document.getElementsByName("datetype");
			selects[1].checked = true;
		}
	});

	function chooseyear() {
		$('#month').hide();
	}

	function choosemonth() {
		$('#month').show();
	}

	function find() {
		var year = $('#year').val();
		var month = $('#month').val();
		if (month.length == 1) {
			month = "0" + month;
		}
		var type = $('input:radio[name=datetype]:checked').val();
		if (type == "year") {
			date = year;
		} else {
			date = year + month;
		}

		window.location.href = '${cp}/statistics/interrogate?date=' + date + '&type=' + type;//下面代码为了说明这个
	}

说明:最先出现的代码的完整版:

@RequestMapping("interrogate")
	public String interrogateindex(String type, String date) {
		Map<String, String> map = findresult(type, date);
		List<StatisticsInterrogate> list = statisticsService.StatisticsInterrogatelist(map);
		request.setAttribute("StatisticsResult", list);
		return "statistics/interrogate";
	}