??工作中遇到這樣一個(gè)需求場(chǎng)景：實(shí)現(xiàn)一個(gè)統(tǒng)計(jì)查詢，要求可以根據(jù)用戶在前端界面篩選的字段進(jìn)行動(dòng)態(tài)地分組統(tǒng)計(jì)。也就是說(shuō)，后端在實(shí)現(xiàn)分組查詢的時(shí)候，Group By 的字段是不確定的，可能是一個(gè)字段、多個(gè)字段或者不進(jìn)行分組查詢，這都是由用戶在前端決定的。

??這里給出的實(shí)現(xiàn)方案：

• 前端界面收集用戶需要分組統(tǒng)計(jì)的字段，然后將這些字段名組成一個(gè)字符串，字段名之間由逗號(hào)分隔，傳遞給后端。
• 后端拿到分組字段名字符串再根據(jù)逗號(hào)分隔符進(jìn)行處理，拼裝成一個(gè)分組字段名列表。
• 最后，利用 Mybatis 框架的動(dòng)態(tài) SQL 語(yǔ)句，實(shí)現(xiàn)動(dòng)態(tài)分組字段的統(tǒng)計(jì)查詢。

控制類(lèi)XxxStatisticsController實(shí)現(xiàn)代碼如下：

@RestController
@RequestMapping("/statistics")
public class XxxStatisticsController {

    @Autowired
    private XxxService xxxService;
   
    @Operation(method = GET_METHOD, summary = "xxx動(dòng)態(tài)分組統(tǒng)計(jì)直方圖", parameters = {
            @Parameter(name = "startDate", description = "開(kāi)始日期,形如:2023-07-01"),
            @Parameter(name = "endDate", description = "結(jié)束日期,形如:2023-07-10"),
            @Parameter(name = "groupFields", description = "需要分組的字段名稱,逗號(hào)分隔，形如“l(fā)evel,title”"),
            @Parameter(name = "title", description = "標(biāo)題")
    })
    @Login
    @GetMapping("/xxxStatistics")
    public Result<Map<String, List<StatisticsDO>>> xxxStatistics(
    														 @RequestParam String startTime,
                                                             @RequestParam String endTime,
                                                             @RequestParam(required = false) String groupFields,
                                                             @RequestParam(required = false) String title) {
        QueryBuilder builder = QueryBuilder.page(0).pageSize(1);                                                    
        // 此處省略若干代碼
        if (StringUtils.isNotBlank(groupFields)) {
            List<String> groupFieldList = Arrays.asList(groupFields.split(Constants.COMMA_SPLIT));
            builder.put("groupFieldList", groupFieldList);
        }
        return xxxService.xxxStatistics(builder.build());
    }
}

xxx-statistics-info-mapper.xml 文件代碼如下：文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-682034.html

<?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="XxxStatisticsInfoMapper">
    <resultMap id="default" type="XxxStatisticsInfoDO">
        <result column="id" property="id"/>
        <result column="title" property="title"/>
        <result column="xxx" property="xxx"/>
        <result column="xxx" property="xxx"/>
        <result column="xxx" property="xxx"/>
        <result column="xxx" property="xxx"/>
        <result column="xxx" property="xxx"/>
        <result column="xxx" property="xxx"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
    </resultMap>
    
    <sql id="query">
        <if test="startTime != null and startTime != ''">
            AND alert_time &gt;= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            AND alert_time &lt;= #{endTime}
        </if>
    </sql>

    <sql id="queryByGroup">
        <if test="groupFieldList != null and groupFieldList.size() > 0">
            <!-- 這里根據(jù)前端傳入的參數(shù)拼接動(dòng)態(tài)的 GROUP BY 子句 -->
            GROUP BY
            <foreach item="field" collection="groupFieldList" separator=",">
                ${field}
            </foreach>
        </if>
    </sql>
    
    <select id="dynamicGroupStatistics" resultType="com.xxx.xxx.domain.DynamicGroupStatisticsDO">
        SELECT
        xxx,
        xxx,
        xxx,
        title,
        xxx,
        xxx,
        count(*) AS xxx_num
        FROM xxx_statistics_info
        <where>
            <include refid="query"/>
            <if test="title != null and title != ''">
                AND title = #{title}
            </if>
        </where>
        <include refid="queryByGroup"/>
        ORDER BY xxx_num DESC
        LIMIT 30
    </select>
</mapper>

到了這里，關(guān)于【已解決】Mybatis 實(shí)現(xiàn) Group By 動(dòng)態(tài)分組查詢的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！