1.报表管理（报表管理：原材料、半成品、成品的数据统计，做成入厂检、车间检、出厂检的数据统计）

liding

2026-03-18 8341ec7982e0d0fdae67ae32c8f55e4292c9605a

1.报表管理（报表管理：原材料、半成品、成品的数据统计，做成入厂检、车间检、出厂检的数据统计）

 src/main/java/com/ruoyi/quality/pojo/QualityUnqualified.java

@@ -136,8 +136,13 @@
    @ApiModelProperty("关联检测id")
    private Long inspectId;


    @ApiModelProperty("是否不合格处理自己新增")
    @TableField(exist = false)
    private Boolean method;

    @ApiModelProperty("批号")
    private Boolean batchNo;

    @ApiModelProperty("检测类型")
    private Integer checkType;
}

 src/main/java/com/ruoyi/quality/pojo/RawMaterial.java

@@ -1,13 +1,6 @@
package com.ruoyi.quality.pojo;

import com.baomidou.mybatisplus.annotation.*;

import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Date;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.ruoyi.framework.aspectj.lang.annotation.Excel;
import io.swagger.annotations.ApiModel;
@@ -15,6 +8,10 @@
import lombok.Getter;
import lombok.Setter;
import org.springframework.format.annotation.DateTimeFormat;

import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.Date;

/**
 * <p>
@@ -47,7 +44,7 @@
    private String batchNo;

    @ApiModelProperty("检验结果 0合格 1不合格")
    @Excel(name = "检验结果", readConverterExp = "0=合格,1=不合格")
    @Excel(name = "检验结果", readConverterExp = "0=不合格,1=合格")
    private Integer checkResult;

    @ApiModelProperty("类别(0:未提交;1:已提交)")

 src/main/java/com/ruoyi/quality/service/impl/QualityReportServiceImpl.java

@@ -1,9 +1,6 @@
package com.ruoyi.quality.service.impl;

import com.ruoyi.basic.service.IProductModelService;
import com.ruoyi.basic.service.IProductService;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.production.service.ProductOrderService;
import com.ruoyi.quality.dto.*;
import com.ruoyi.quality.mapper.QualityInspectMapper;
import com.ruoyi.quality.service.QualityReportService;

 src/main/resources/mapper/quality/QualityInspectMapper.xml

@@ -75,369 +75,277 @@
    </delete>

    <select id="getInspectStatistics" resultType="com.ruoyi.quality.dto.QualityInspectStatDto">
        SELECT CASE pp.product_name
                   WHEN '原材料' THEN 0
                   WHEN '半成品' THEN 1
                   WHEN '成品' THEN 2
                   END                     AS modelType,

               IFNULL(SUM(qi.quantity), 0) AS totalCount,

               IFNULL(SUM(CASE
                              WHEN qi.inspect_state = 1 THEN qi.quantity
                              ELSE 0
                   END), 0)                AS completedCount

        FROM product p
                 INNER JOIN product pp
                            ON p.parent_id = pp.id
                 LEFT JOIN product_model pm
                           ON pm.product_id = p.id
                 LEFT JOIN quality_inspect qi
                           ON qi.product_model_id = pm.id

        WHERE pp.product_name IN ('原材料', '半成品', '成品')

        GROUP BY pp.product_name

        SELECT
            rm.check_type AS modelType,
            COUNT(*) AS totalCount,
            SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) AS completedCount,
            SUM(CASE WHEN rm.inspect_state = 0 THEN 1 ELSE 0 END) AS uncommittedCount
        FROM raw_material rm
        WHERE rm.inspect_state IN (0, 1)
        GROUP BY rm.check_type
        ORDER BY rm.check_type;
    </select>

    <select id="getPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityPassRateDto">
        SELECT t.modelType,

               COALESCE(SUM(qi.quantity), 0) AS totalCount,

               COALESCE(SUM(
                                CASE
                                    WHEN qi.inspect_state = 1 THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0)                AS completedCount,

               COALESCE(SUM(
                                CASE
                                    WHEN qi.inspect_state = 1
                                        AND qi.check_result = '合格'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0)                AS qualifiedCount,

               COALESCE(SUM(
                                CASE
                                    WHEN qi.inspect_state = 1
                                        AND qi.check_result = '不合格'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0)                AS unqualifiedCount,

            /* 完成率 */
               IF(COALESCE(SUM(qi.quantity), 0) = 0, 0,
                  ROUND(
                          COALESCE(SUM(
                                           CASE
                                               WHEN qi.inspect_state = 1 THEN qi.quantity
                                               ELSE 0
                                               END
                                   ), 0)
                              / SUM(qi.quantity) * 100, 2)
               )                             AS completionRate,

            /* 合格率 */
               IF(COALESCE(SUM(
                                   CASE
                                       WHEN qi.inspect_state = 1 THEN qi.quantity
                                       ELSE 0
                                       END
                           ), 0) = 0, 0,
                  ROUND(
                          COALESCE(SUM(
                                           CASE
                                               WHEN qi.inspect_state = 1
                                                   AND qi.check_result = '合格'
                                                   THEN qi.quantity
                                               ELSE 0
                                               END
                                   ), 0)
                              /
                          COALESCE(SUM(
                                           CASE
                                               WHEN qi.inspect_state = 1 THEN qi.quantity
                                               ELSE 0
                                               END
                                   ), 0) * 100, 2)
               )                             AS passRate

        FROM (SELECT 0 AS modelType
              UNION ALL
              SELECT 1
              UNION ALL
              SELECT 2) t
                 LEFT JOIN product p
                           ON 1 = 1
                 LEFT JOIN product pp
                           ON p.parent_id = pp.id
                 LEFT JOIN product_model pm
                           ON pm.product_id = p.id
                 LEFT JOIN quality_inspect qi
                           ON qi.product_model_id = pm.id
                               AND (
                                  (pp.product_name = '原材料' AND t.modelType = 0) OR
                                  (pp.product_name = '半成品' AND t.modelType = 1) OR
                                  (pp.product_name = '成品' AND t.modelType = 2)
                                  )

        GROUP BY t.modelType
        SELECT
            t.modelType,
            COALESCE(rm.totalCount, 0) AS totalCount,
            COALESCE(rm.completedCount, 0) AS completedCount,
            COALESCE(rm.qualifiedCount, 0) AS qualifiedCount,
            COALESCE(rm.unqualifiedCount, 0) AS unqualifiedCount,
            COALESCE(rm.uncommittedCount, 0) AS uncommittedCount,
            COALESCE(rm.completionRate, 0) AS completionRate,
            COALESCE(rm.passRate, 0) AS passRate
        FROM (
                 SELECT 0 AS modelType
                 UNION ALL
                 SELECT 1
                 UNION ALL
                 SELECT 2
             ) t
                 LEFT JOIN (
            SELECT
                rm.check_type AS modelType,
                COUNT(*) AS totalCount,
                SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) AS completedCount,
                SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 1 THEN 1 ELSE 0 END) AS qualifiedCount,
                SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 0 THEN 1 ELSE 0 END) AS unqualifiedCount,
                SUM(CASE WHEN rm.inspect_state = 0 THEN 1 ELSE 0 END) AS uncommittedCount,
                IF(COUNT(*) = 0, 0,
                   ROUND(SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2)
                ) AS completionRate,
                IF(SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) = 0, 0,
                   ROUND(SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 1 THEN 1 ELSE 0 END) /
                         SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) * 100, 2)
                ) AS passRate
            FROM raw_material rm
            GROUP BY rm.check_type
        ) rm ON t.modelType = rm.modelType
        ORDER BY t.modelType;
    </select>


    <select id="getMonthlyPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityMonthlyPassRateDto">
        WITH RECURSIVE
            months AS (SELECT 1 AS month_num
                       UNION ALL
                       SELECT month_num + 1
                       FROM months
                       WHERE month_num &lt; 12),
            types AS (SELECT 0 AS modelType
                      UNION ALL
                      SELECT 1
                      UNION ALL
                      SELECT 2),
            base AS (SELECT m.month_num, t.modelType
                     FROM months m
                              CROSS JOIN types t)

        SELECT CASE b.month_num
                   WHEN 1 THEN '一月'
                   WHEN 2 THEN '二月'
                   WHEN 3 THEN '三月'
                   WHEN 4 THEN '四月'
                   WHEN 5 THEN '五月'
                   WHEN 6 THEN '六月'
                   WHEN 7 THEN '七月'
                   WHEN 8 THEN '八月'
                   WHEN 9 THEN '九月'
                   WHEN 10 THEN '十月'
                   WHEN 11 THEN '十一月'
                   WHEN 12 THEN '十二月'
                   END                       AS month,

               b.modelType,

            /* 总数量 */
               COALESCE(SUM(qi.quantity), 0) AS totalCount,

            /* 已完成 */
               COALESCE(SUM(
                                CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END
                        ), 0)                AS completedCount,

            /* 合格 */
               COALESCE(SUM(
                                CASE
                                    WHEN qi.inspect_state = 1 AND qi.check_result = '合格'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0)                AS qualifiedCount,

            /* 不合格 */
               COALESCE(SUM(
                                CASE
                                    WHEN qi.inspect_state = 1 AND qi.check_result = '不合格'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0)                AS unqualifiedCount,

            /* 完成率 */
               IF(COALESCE(SUM(qi.quantity), 0) = 0, 0,
                  ROUND(
                          COALESCE(SUM(
                                           CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END
                                   ), 0) / SUM(qi.quantity) * 100, 2
                  )
               )                             AS completionRate,

            /* 合格率 */
               IF(COALESCE(SUM(
                                   CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END
                           ), 0) = 0, 0,
                  ROUND(
                          COALESCE(SUM(
                                           CASE
                                               WHEN qi.inspect_state = 1 AND qi.check_result = '合格'
                                                   THEN qi.quantity
                                               ELSE 0
                                               END
                                   ), 0)
                              /
                          COALESCE(SUM(
                                           CASE WHEN qi.inspect_state = 1 THEN qi.quantity ELSE 0 END
                                   ), 0) * 100, 2
                  )
               )                             AS passRate

        months AS (
        SELECT 1 AS month_num
        UNION ALL
        SELECT month_num + 1
        FROM months
        WHERE month_num &lt; 12
        ),
        types AS (
        SELECT 0 AS modelType
        UNION ALL
        SELECT 1
        UNION ALL
        SELECT 2
        ),
        base AS (
        SELECT m.month_num, t.modelType
        FROM months m
        CROSS JOIN types t
        )
        SELECT
        CASE b.month_num
        WHEN 1 THEN '一月'
        WHEN 2 THEN '二月'
        WHEN 3 THEN '三月'
        WHEN 4 THEN '四月'
        WHEN 5 THEN '五月'
        WHEN 6 THEN '六月'
        WHEN 7 THEN '七月'
        WHEN 8 THEN '八月'
        WHEN 9 THEN '九月'
        WHEN 10 THEN '十月'
        WHEN 11 THEN '十一月'
        WHEN 12 THEN '十二月'
        END AS month,
        b.modelType,
        COALESCE(rm.totalCount, 0) AS totalCount,
        COALESCE(rm.completedCount, 0) AS completedCount,
        COALESCE(rm.qualifiedCount, 0) AS qualifiedCount,
        COALESCE(rm.unqualifiedCount, 0) AS unqualifiedCount,
        COALESCE(rm.uncommittedCount, 0) AS uncommittedCount,
        COALESCE(rm.completionRate, 0) AS completionRate,
        COALESCE(rm.passRate, 0) AS passRate
        FROM base b
                 LEFT JOIN product p ON 1 = 1
                 LEFT JOIN product pp ON p.parent_id = pp.id
                 LEFT JOIN product_model pm ON pm.product_id = p.id
                 LEFT JOIN quality_inspect qi
                           ON qi.product_model_id = pm.id
                               AND YEAR(qi.check_time) = #{year}
                               AND MONTH(qi.check_time) = b.month_num
                               AND (
                                  (pp.product_name = '原材料' AND b.modelType = 0)
                                      OR (pp.product_name = '半成品' AND b.modelType = 1)
                                      OR (pp.product_name = '成品' AND b.modelType = 2)
                                  )

        GROUP BY b.month_num, b.modelType
        LEFT JOIN (
        SELECT
        MONTH(rm.create_time) AS month_num,
        rm.check_type AS modelType,
        COUNT(*) AS totalCount,
        SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) AS completedCount,
        SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 1 THEN 1 ELSE 0 END) AS qualifiedCount,
        SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 0 THEN 1 ELSE 0 END) AS unqualifiedCount,
        SUM(CASE WHEN rm.inspect_state = 0 THEN 1 ELSE 0 END) AS uncommittedCount,
        IF(COUNT(*) = 0, 0,
        ROUND(SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2)
        ) AS completionRate,
        IF(SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) = 0, 0,
        ROUND(SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 1 THEN 1 ELSE 0 END) /
        SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) * 100, 2)
        ) AS passRate
        FROM raw_material rm
        WHERE YEAR(rm.create_time) = #{year}  -- 按年份筛选
        GROUP BY MONTH(rm.create_time), rm.check_type
        ) rm ON b.month_num = rm.month_num AND b.modelType = rm.modelType
        ORDER BY b.month_num, b.modelType;

    </select>

    <select id="getYearlyPassRateStatistics" resultType="com.ruoyi.quality.dto.QualityPassRateDto">
        SELECT t.modelType,

               COALESCE(SUM(
                                CASE
                                    WHEN pp.product_name = '原材料' AND t.modelType = 0 THEN qi.quantity
                                    WHEN pp.product_name = '半成品' AND t.modelType = 1 THEN qi.quantity
                                    WHEN pp.product_name = '成品' AND t.modelType = 2 THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0) AS totalCount

        FROM (SELECT 0 AS modelType
              UNION ALL
              SELECT 1
              UNION ALL
              SELECT 2) t
                 LEFT JOIN product p ON 1 = 1
                 LEFT JOIN product pp ON p.parent_id = pp.id
                 LEFT JOIN product_model pm ON pm.product_id = p.id
                 LEFT JOIN quality_inspect qi
                           ON qi.product_model_id = pm.id
                               AND YEAR(qi.check_time) = #{year}
                               AND qi.inspect_state = 1
                               AND qi.check_result = '合格'

        GROUP BY t.modelType
        SELECT
            t.modelType,
            COALESCE(rm.totalCount, 0) AS totalCount,
            COALESCE(rm.completedCount, 0) AS completedCount,
            COALESCE(rm.uncommittedCount, 0) AS uncommittedCount,
            COALESCE(rm.qualifiedCount, 0) AS qualifiedCount,
            COALESCE(rm.unqualifiedCount, 0) AS unqualifiedCount,
            COALESCE(rm.completionRate, 0) AS completionRate,
            COALESCE(rm.passRate, 0) AS passRate
        FROM (
                 SELECT 0 AS modelType
                 UNION ALL
                 SELECT 1
                 UNION ALL
                 SELECT 2
             ) t
                 LEFT JOIN (
            SELECT
                rm.check_type AS modelType,
                COUNT(*) AS totalCount,
                SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) AS completedCount,
                SUM(CASE WHEN rm.inspect_state = 0 THEN 1 ELSE 0 END) AS uncommittedCount,
                SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 1 THEN 1 ELSE 0 END) AS qualifiedCount,
                SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 0 THEN 1 ELSE 0 END) AS unqualifiedCount,
                -- 完成率 = 已提交数量 / 总数量 * 100
                IF(COUNT(*) = 0, 0,
                   ROUND(SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2)
                ) AS completionRate,
                -- 合格率 = 合格数量 / 已提交数量 * 100
                IF(SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) = 0, 0,
                   ROUND(SUM(CASE WHEN rm.inspect_state = 1 AND rm.check_result = 1 THEN 1 ELSE 0 END) /
                         SUM(CASE WHEN rm.inspect_state = 1 THEN 1 ELSE 0 END) * 100, 2)
                ) AS passRate
            FROM raw_material rm
            WHERE rm.inspect_state IN (0, 1)
              AND YEAR(rm.create_time) = #{year}  -- 按年份筛选，假设有时间字段
            GROUP BY rm.check_type
        ) rm ON t.modelType = rm.modelType
        ORDER BY t.modelType;
    </select>



    <select id="getMonthlyCompletionDetails" resultType="com.ruoyi.quality.dto.QualityMonthlyDetailDto">
        WITH RECURSIVE months AS (SELECT 1 AS month_num
                                  UNION ALL
                                  SELECT month_num + 1
                                  FROM months
                                  WHERE month_num &lt; 12)
        SELECT CASE m.month_num
                   WHEN 1 THEN '一月'
                   WHEN 2 THEN '二月'
                   WHEN 3 THEN '三月'
                   WHEN 4 THEN '四月'
                   WHEN 5 THEN '五月'
                   WHEN 6 THEN '六月'
                   WHEN 7 THEN '七月'
                   WHEN 8 THEN '八月'
                   WHEN 9 THEN '九月'
                   WHEN 10 THEN '十月'
                   WHEN 11 THEN '十一月'
                   WHEN 12 THEN '十二月'
                   END        AS month,

            /* 原材料 */
               COALESCE(SUM(
                                CASE
                                    WHEN pp.product_name = '原材料'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0) AS rawMaterialCount,

            /* 半成品 */
               COALESCE(SUM(
                                CASE
                                    WHEN pp.product_name = '半成品'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0) AS processCount,

            /* 成品 */
               COALESCE(SUM(
                                CASE
                                    WHEN pp.product_name = '成品'
                                        THEN qi.quantity
                                    ELSE 0
                                    END
                        ), 0) AS outgoingCount

        WITH RECURSIVE months AS (
        SELECT 1 AS month_num
        UNION ALL
        SELECT month_num + 1
        FROM months
        WHERE month_num &lt; 12
        )
        SELECT
        CASE m.month_num
        WHEN 1 THEN '一月'
        WHEN 2 THEN '二月'
        WHEN 3 THEN '三月'
        WHEN 4 THEN '四月'
        WHEN 5 THEN '五月'
        WHEN 6 THEN '六月'
        WHEN 7 THEN '七月'
        WHEN 8 THEN '八月'
        WHEN 9 THEN '九月'
        WHEN 10 THEN '十月'
        WHEN 11 THEN '十一月'
        WHEN 12 THEN '十二月'
        END AS month,
        /* 入场检 (check_type = 0) */
        COALESCE(SUM(
        CASE
        WHEN rm.check_type = 0
        THEN 1  -- 统计记录数，因为原SQL用的是quantity字段
        ELSE 0
        END
        ), 0) AS rawMaterialCount,
        /* 车间检 (check_type = 1) */
        COALESCE(SUM(
        CASE
        WHEN rm.check_type = 1
        THEN 1
        ELSE 0
        END
        ), 0) AS processCount,
        /* 出厂检 (check_type = 2) */
        COALESCE(SUM(
        CASE
        WHEN rm.check_type = 2
        THEN 1
        ELSE 0
        END
        ), 0) AS outgoingCount
        FROM months m
                 LEFT JOIN product p ON 1 = 1
                 LEFT JOIN product pp ON p.parent_id = pp.id
                 LEFT JOIN product_model pm ON pm.product_id = p.id
                 LEFT JOIN quality_inspect qi
                           ON qi.product_model_id = pm.id
                               AND qi.inspect_state = 1
                               AND YEAR(qi.check_time) = #{year}
                               AND MONTH(qi.check_time) = m.month_num

        LEFT JOIN raw_material rm
        ON YEAR(rm.create_time) = #{year}
        AND MONTH(rm.create_time) = m.month_num
        AND rm.inspect_state = 1      -- 已提交
        AND rm.check_result = 1       -- 合格
        GROUP BY m.month_num
        ORDER BY m.month_num;

    </select>

    <select id="getTopParameters" resultType="com.ruoyi.quality.dto.QualityParameterStatDto">
        WITH parameter_counts AS (SELECT qip.parameter_item AS name,
                                         COUNT(*)           AS count
                                  FROM quality_inspect_param qip
                                           JOIN quality_inspect qi
                                                ON qip.inspect_id = qi.id
                                           JOIN product p
                                                ON qi.product_id = p.id
                                           JOIN product pp
                                                ON p.parent_id = pp.id
                                  WHERE qi.inspect_state = 1
                                    AND (
                                      (#{modelType} = 1 AND pp.product_name = '原材料')
                                          OR (#{modelType} = 2 AND pp.product_name = '半成品')
                                          OR (#{modelType} = 3 AND pp.product_name = '成品')
                                      )
                                  GROUP BY qip.parameter_item),
             ranked AS (SELECT name,
                               count,
                               ROW_NUMBER() OVER (ORDER BY count DESC) AS rn
                        FROM parameter_counts),
             total AS (SELECT SUM(count) AS total_count
                       FROM parameter_counts)
        WITH parameter_counts AS (
        SELECT
        qii.name,
        COUNT(*) AS count
        FROM raw_material rm
        LEFT JOIN raw_material_quality_inspect_item rmqi
        ON rmqi.raw_material_id = rm.id
        LEFT JOIN quality_inspect_item qii
        ON qii.id = rmqi.quality_inspect_item_id
        WHERE qii.name IS NOT NULL
        AND rm.check_type = CASE
        WHEN #{modelType} = 1 THEN 0   -- 前端1 → 数据库0
        WHEN #{modelType} = 2 THEN 1   -- 前端2 → 数据库1
        WHEN #{modelType} = 3 THEN 2   -- 前端3 → 数据库2
        END
        GROUP BY qii.name
        ),
        ranked AS (
        SELECT
        name,
        count,
        ROW_NUMBER() OVER (ORDER BY count DESC) AS rn
        FROM parameter_counts
        ),
        total AS (
        SELECT SUM(count) AS total_count
        FROM parameter_counts
        )

        SELECT name,
               count,
               CASE
                   WHEN (SELECT total_count FROM total) = 0 THEN 0
                   ELSE ROUND(count / (SELECT total_count FROM total) * 100, 2)
                   END AS percentage
        FROM (SELECT name, count, rn
              FROM ranked
              WHERE rn &lt;= 4

              UNION ALL

              SELECT '其他检测' AS name,
                     SUM(count) AS count,
                     5          AS rn
              FROM ranked
              WHERE rn > 4
              HAVING SUM(count) > 0) t
        SELECT
        name,
        count,
        CASE
        WHEN (SELECT total_count FROM total) = 0 THEN 0
        ELSE ROUND(count / (SELECT total_count FROM total) * 100, 2)
        END AS percentage
        FROM (
        SELECT name, count, rn
        FROM ranked
        WHERE rn &lt;= 4
        UNION ALL
        -- 其他合并
        SELECT
        '其他检测' AS name,
        SUM(count) AS count,
        5 AS rn
        FROM ranked
        WHERE rn > 4
        HAVING SUM(count) > 0
        ) t
        ORDER BY rn;

    </select>



 src/main/resources/mapper/quality/QualityUnqualifiedMapper.xml

@@ -16,6 +16,7 @@
        qu.deal_result,
        qu.deal_name,
        qu.deal_time,
        qu.batch_no,
        CASE
        WHEN qu.model = pm.id THEN pm.model
        ELSE qu.model