列表查询和分组统计字段其他类型

示例： 

--列表查询
--字段单选
<if test='byqrzgxwF != null and byqrzgxwF != "" and byqrzgxwF != "其他"'>
	AND a.byqrzgxw = #{byqrzgxwF}
</if>
<if test='byqrzgxwF != null and byqrzgxwF != "" and byqrzgxwF == "其他" '>
	AND decode(a.byqrzgxw,'本科',1,'硕士',2,'博士',3 ,0) = 0
</if>

--字段多选
<if test="lfsyF != null and lfsyF != '' and lfsyF !='其他'">
	AND a.lfsy LIKE '%'||#{lfsyF}||'%'
</if>
<if test="lfsyF != null and lfsyF != '' and lfsyF =='其他'">
	AND (a.lfsy like '%其他%' or a.lfsy is null
		or (a.lfsy not like '%授课%' and a.lfsy not like '%短期讲学%' 
            and a.lfsy not like '%合作研究%' and a.lfsy not like '%学术访问%'
			and a.lfsy not like '%工作访问%' and a.lfsy not like '%国际会议%' 
            and a.lfsy not like '%培训%' and a.lfsy not like '%其他%' 
            and a.lfsy is not null)
		)
</if>


--类型分组统计
--字段单选
SELECT dz,
		NVL(sum(decode(sfyc,'是',1,0)),0) as "是",
		NVL(sum(decode(sfyc,'否',1,0)),0) as "否",
		NVL(sum(decode(sfyc,'是',0,'否',0,'',1,1)),0) as "其他"
FROM LFJD 
GROUP BY dz;


--字段多选（字段两侧拼逗号防止出现教授与副教授重复被计算）
SELECT dz,
		NVL(sum(decode(sign(INSTR(','||sfyc||',',',是,')-1),1,1,0,1,0)),0) as "是",
		NVL(sum(decode(sign(INSTR(','||sfyc||',',',否,')-1),1,1,0,1,0)),0) as "否",
		NVL(sum(decode(sign(INSTR(','||sfyc||',',',其他,')-1),1,1,0,1,0)),0) +             
          NVL(sum(decode(sfyc,'',1)),0) + 
		  NVL(sum(decode(sign
                    (
                    INSTR(','||sfyc||',',',是,')+
                    INSTR(','||sfyc||',',',否,')+
                    INSTR(','||sfyc||',',',其他,')+
                    decode(sfyc,'',1,0)
                    ),0,1,0)
                 ),0) as "其他"
FROM LFJD 
GROUP BY dz;