SELECT
case_id,
sum(CASE WHEN execute_result = 1 THEN 1
ELSE 0 END) success_count,
sum(CASE WHEN execute_result = 3 THEN 1
ELSE 0 END) block_count,
sum(CASE WHEN execute_result = 2 THEN 1
ELSE 0 END) failed_count,
count(*) all_count
FROM report
1
case
when ...then...
when...then...
else...end
2
UNION / UNION ALL
合并两个查询的结果集
union 合并出来的数据不重复
union all 合并时不去重
3
含有text类型的表字段 用mybatis生成的xml文件里 单独将这个字段提出来
<mapper namespace="com......FaultMapper">
<resultMap id="BaseResultMap" type="com......FaultMapper">
<id column="fault_id" jdbcType="BIGINT" property="faultId" />
...
<result column="create_uid" jdbcType="BIGINT" property="createUid" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
<result column="update_uid" jdbcType="BIGINT" property="updateUid" />
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
<result column="state" jdbcType="VARCHAR" property="state" />
</resultMap>
//fault_desc字段类型为text 单独有个包含此字段的resultMap xxxWithBLOBs
<resultMap extends="BaseResultMap" id="ResultMapWithBLOBs" type="com......FaultMapper">
<result column="fault_desc" jdbcType="LONGVARCHAR" property="faultDesc" />
</resultMap>
对应的Mapper接口文件中也分两种返回类型的方法
selectByExampleWithBLOBs selectByExample
selectByPrimaryKeyWithBLOBs selectByPrimaryKey
updateByExampleWithBLOBs updateByExample
updateByPrimaryKeyWithBLOBs updateByPrimaryKey