分组关联条件查询，自定义mybatis映射

2019独角兽企业重金招聘Python工程师标准>>>

已知表格结构： 

CREATE TABLE `challengedata` (
  `id` varchar(32) NOT NULL COMMENT '主键id',
  `match_name` varchar(100) NOT NULL COMMENT '赛事名称',
  `match_area` varchar(100) NOT NULL COMMENT '赛区名称',
  `team_a_name` varchar(100) NOT NULL COMMENT 'A队名称',
  `team_b_name` varchar(100) NOT NULL COMMENT 'B队名称',
  `team_type` int(11) NOT NULL COMMENT '队伍类别 （0女子,1男子）',
  `match_date` varchar(255) DEFAULT NULL COMMENT '比赛日期',
  `match_status_time` varchar(255) DEFAULT NULL COMMENT '比赛状态时间',
  `challenger` varchar(100) DEFAULT NULL COMMENT '挑战方',
  `challenge_content` int(11) DEFAULT NULL COMMENT '挑战内容  1-14',
  `challenge_start_time` varchar(255) DEFAULT NULL COMMENT '挑战开始时间',
  `challenge_end_time` varchar(255) DEFAULT NULL COMMENT '挑战结束时间',
  `challenge_use_time` varchar(255) DEFAULT '0' COMMENT '挑战时长',
  `challenge_result` int(11) DEFAULT NULL COMMENT '挑战结果：0失败，1成功',
  `challenge_video_name` varchar(100) DEFAULT NULL COMMENT '挑战视频名称',
  `challenge_upload_url` varchar(255) DEFAULT NULL COMMENT '视频上传地址',
  `transfer_status` int(11) DEFAULT NULL COMMENT '传输状态',
  `match_status` int(11) DEFAULT NULL COMMENT '比赛状态'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='挑战数据表';

测试表格数据“” 

挑战种类表：

需要得到的内容:

1.下图中的表格，按照挑战内容分组，计算每种挑战的成功次数、成功率、失败次数、失败率、总字数，显示成表格。

2.下图中红框中的数据：总比赛数，总挑战数，总挑战成功数，总挑战失败数。

以上数据必须支持多条件查询，条件为：match_name（比赛名称）, match_area（区域名称）, team_name（队伍名称）, team_type（比赛类型）, match_date（比赛日期）

经过思考设计如下：

mapper接口（）：

package org.rgdata.dao;

import java.util.LinkedHashMap;
import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.rgdata.vo.ChallengeStatisticsVo;
import org.rgdata.vo.MCSummaryVo;
import org.rgdata.vo.SqlVo;

/**
 * @Title:SQLMapper
 * @Description:跨表或通用sql映射
 * @author 张颖辉
 * @date 2017年9月28日下午2:03:23
 * @version 1.0
 */
public interface SQLMapper {

	/**
	 * @Title:依据条件查询并统计各分类挑战的胜败数以及概率
	 * @Description:Comment for non-overriding methods
	 * @author 张颖辉
	 * @date 2017年9月28日下午2:27:23
	 * @param match_name
	 * @param match_area
	 * @param team_name
	 * @param team_type
	 * @param match_date
	 * @return
	 */
	List<ChallengeStatisticsVo> statistics(@Param("match_name") String match_name,
			@Param("match_area") String match_area, @Param("team_name") String team_name,
			@Param("team_type") Integer team_type, @Param("match_date") String match_date);

	/**
	 * @Title:依据条件获取 总比赛数，总挑战数，总挑战成功数，总挑战失败数
	 * @Description:Comment for non-overriding methods
	 * @author 张颖辉
	 * @date 2017年9月28日下午2:28:03
	 * @param match_name
	 * @param match_area
	 * @param team_name
	 * @param team_type
	 * @param match_date
	 * @return
	 */
	MCSummaryVo matchChallengeSummary(@Param("match_name") String match_name, @Param("match_area") String match_area,
			@Param("team_name") String team_name, @Param("team_type") Integer team_type,
			@Param("match_date") String match_date);
}

SQLMapper.xml

其中<sql> 标签内的sql语句块，为共享的公共sql

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.rgdata.dao.SQLMapper">
	
	<!--  #####  挑战数据统计 -zyh #####-->
	<select id="statistics"  parameterType="map"  resultType="org.rgdata.vo.ChallengeStatisticsVo">
  	select c.content_name challengeContent, sum(case  when challenge_result=1 then 1 else 0 end) successNumber, round((sum(case  when challenge_result=1 then 1 else 0 end)/count(d.id))*100,2) successRate,sum(case  when challenge_result=0 then 1 else 0 end) failureNumber ,round((sum(case  when challenge_result=0 then 1 else 0 end)/count(d.id))*100,2) failureRate,count(d.id) totalNumber  
		from  challengecontent c RIGHT JOIN (<include refid="Select_Challengedata__Where_Clause" />) d  on d.challenge_content=c.id GROUP BY c.content_name having c.content_name is not null
	</select>
	
	<sql id="Select_Challengedata__Where_Clause">
		select * from challengedata  where 1=1
		<if test="match_name != null">
			 and match_name=#{match_name}
		</if>
		<if test="match_area != null">
			 and match_area=#{match_area}
		</if>
		<if test="team_name != null">
			 and (team_a_name like CONCAT('%',#{team_name},'%') or team_b_name like CONCAT('%',#{team_name},'%') )
		</if>
		<if test="team_type != null">
			 and team_type=#{team_type}
		</if>
		<if test="match_date != null">
			 and match_date=#{match_date}
		</if>
	</sql>
	
	
	<!--  #####  比赛/挑战总结统计 -zyh ##### 。。。。。-->
	<select id="matchChallengeSummary" parameterType="map"  resultType="org.rgdata.vo.MCSummaryVo">
  	 select  SUM(case when cd.challenge_content is null then 1 else 0 end) matchNum,  SUM(case when cd.challenge_content is not null then 1 else 0 end) challengeNum,SUM(case when cd.challenge_content is not null and cd.challenge_result=1 then 1 else 0 end) sChallengeNum ,SUM(case when cd.challenge_content is not null and cd.challenge_result=0 then 1 else 0 end) fChallengeNum from (<include refid="Select_Challengedata__Where_Clause" />) cd
	</select>
</mapper>

依赖的VO类：

ChallengeStatisticsVo.java

package org.rgdata.vo;

/**
 * @Title:ChallengeStatisticsVo
 * @Description:Comment for created type
 * @author 张颖辉
 * @date 2017年9月27日下午2:08:25
 * @version 1.0
 */
public class ChallengeStatisticsVo {
	private String challengeContent;
	private int successNumber;
	private double successRate;
	private int failureNumber;
	private double failureRate;
	private int totalNumber;

	public ChallengeStatisticsVo() {
		super();
	}

	public ChallengeStatisticsVo(String challengeContent, int successNumber, double successRate, int failureNumber,
			double failureRate, int totalNumber) {
		super();
		this.challengeContent = challengeContent;
		this.successNumber = successNumber;
		this.successRate = successRate;
		this.failureNumber = failureNumber;
		this.failureRate = failureRate;
		this.totalNumber = totalNumber;
	}

	public String getChallengeContent() {
		return challengeContent;
	}

	public void setChallengeContent(String challengeContent) {
		this.challengeContent = challengeContent;
	}

	public int getSuccessNumber() {
		return successNumber;
	}

	public void setSuccessNumber(int successNumber) {
		this.successNumber = successNumber;
	}

	public double getSuccessRate() {
		return successRate;
	}

	public void setSuccessRate(double successRate) {
		this.successRate = successRate;
	}

	public int getFailureNumber() {
		return failureNumber;
	}

	public void setFailureNumber(int failureNumber) {
		this.failureNumber = failureNumber;
	}

	public double getFailureRate() {
		return failureRate;
	}

	public void setFailureRate(double failureRate) {
		this.failureRate = failureRate;
	}

	public int getTotalNumber() {
		return totalNumber;
	}

	public void setTotalNumber(int totalNumber) {
		this.totalNumber = totalNumber;
	}

	@Override
	public String toString() {
		return "ChallengeStatisticsVo [challengeContent=" + challengeContent + ", successNumber=" + successNumber
				+ ", successRate=" + successRate + ", failureNumber=" + failureNumber + ", failureRate=" + failureRate
				+ ", totalNumber=" + totalNumber + "]";
	}

}

MCSummaryVo.java

package org.rgdata.vo;

/**
 * @Title:比赛挑战摘要总结
 * @Description:Comment for created type
 * @author 张颖辉
 * @date 2017年9月28日上午9:58:59
 * @version 1.0
 */
public class MCSummaryVo {

	private long matchNum;// 比赛次数
	private long challengeNum;// 挑战次数
	private long sChallengeNum;// 挑战成功数
	private long fChallengeNum;// 挑战失败数

	public long getMatchNum() {
		return matchNum;
	}

	public void setMatchNum(long matchNum) {
		this.matchNum = matchNum;
	}

	public long getChallengeNum() {
		return challengeNum;
	}

	public void setChallengeNum(long challengeNum) {
		this.challengeNum = challengeNum;
	}

	public long getsChallengeNum() {
		return sChallengeNum;
	}

	public void setsChallengeNum(long sChallengeNum) {
		this.sChallengeNum = sChallengeNum;
	}

	public long getfChallengeNum() {
		return fChallengeNum;
	}

	public void setfChallengeNum(long fChallengeNum) {
		this.fChallengeNum = fChallengeNum;
	}

	@Override
	public String toString() {
		return "MCSummaryVo [matchNum=" + matchNum + ", challengeNum=" + challengeNum + ", sChallengeNum="
				+ sChallengeNum + ", fChallengeNum=" + fChallengeNum + "]";
	}

}

 

转载于:https://my.oschina.net/iyinghui/blog/1544748