sql

@sql
1.比率

SELECT
	test.A,
	ROUND( COUNT( CASE WHEN test.B LIKE "%A%" THEN 1 END ) / COUNT( 1 ) * 100, 2 ) AS A 
FROM
	test

2.字段为null返回‘其他’，及查询总数

 SELECT
        COALESCE(rs.nationality, '总数' ) nationality,
        COUNT(*) sum
        FROM
        (SELECT IF(LENGTH(nationality)>0,nationality,'其他' ) AS nationality,
        report_new AS report_new,
        report_severity AS report_severity,
        date_month AS date_month,
        reserve1 AS reserve1,
        province AS province
 FROM
        origin_report

3.计数

 SELECT
        rs.product_batch productBatch,
        COUNT( CASE WHEN rs.report_new LIKE '新的' AND rs.report_severity LIKE '严重' THEN 1 END ) AS levalA,
        COUNT( CASE WHEN rs.report_new LIKE '新的' AND rs.report_severity LIKE '一般' THEN 1 END ) AS levalB,
        COUNT( CASE WHEN rs.report_severity LIKE '一般' AND rs.report_new LIKE '-' THEN 1 END ) AS levalC,
        COUNT( CASE WHEN rs.report_severity LIKE '严重' AND rs.report_new LIKE '-' THEN 1 END ) AS levalD,
        COUNT(*) sum
 FROM
        origin_report  

4.java项目代码块

在这里插入代码片
<sql id="report_origin_Condition">
        <if test="reportNew != null  and reportNew != '' ">
            AND rs.report_new LIKE CONCAT('%',#{reportNew},'%')
        </if>
        <if test="reportSeverity != null  and reportSeverity != '' ">
            and rs.report_severity LIKE CONCAT('%',#{reportSeverity},'%')
        </if>
        <if test="reactionList!= null and reactionList.size()>0">
            and reserve1 in
            <foreach item="reserve1" index="index" collection="reactionList" open="(" separator="," close=")">
                #{reserve1}
            </foreach>
        </if>
        <if test="provinceList!= null and provinceList.size()>0">
            and province in
            <foreach item="province" index="index" collection="provinceList" open="(" separator="," close=")">
                #{province}
            </foreach>
        </if>
        <if test="startTime!= null and startTime != ''">and DATE_FORMAT(rs.date_month,'%Y-%m') >= #{startTime}</if>
        <if test="endTime!= null and endTime != ''">and DATE_FORMAT(rs.date_month,'%Y-%m') <![CDATA[<=]]>#{endTime}</if>
    </sql>


        <include refid="report_origin_Condition"></include>