Mybatis-plus的QueryWrapper拼接多个and 其中子条件中包含or

zhanghe687

已于 2024-02-22 17:21:59 修改

阅读量1.1k

点赞数 8

文章标签： mybatis java

于 2024-02-22 17:21:04 首次发布

本文链接：https://blog.csdn.net/zhanghe687/article/details/136239226

版权

文章描述了如何使用QueryWrapper在数据库中查询特定用户账户的活动数据，包括作品数、延迟次数等，并对近45天的数据进行分组和排序的过程。

摘要由CSDN通过智能技术生成

 //根据id找到关联账号
        QueryWrapper<UserA> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("cyber_start_id", id);
        queryWrapper.eq("expire", 1);

        queryWrapper.select("user_id", "platform");
        List<UserA> list = userAccountInfoService.list(queryWrapper);
        if(list != null && list.size() > 0){

            QueryWrapper<UserA> statUserAccountQueryWrapper = new QueryWrapper<>();
            statUserAccountQueryWrapper.select("stat_time", "sum(works) as works", "sum(rtt_count) as rtt_count", "sum(comment_count) as comment_count",
                    "sum(like_count) as like_count", "sum(fans) as fans");
            //近45天的
            String stime = TimeUtil.getBeforeDay(45);
            String etime = TimeUtil.getBeforeDay(-1);
            statUserAccountQueryWrapper.between("stat_time", stime, etime);

            //子条件
            statUserAccountQueryWrapper.nested(i ->
                    i.and(j -> {
                        for (UserA userAccountInfo : list) {
                            String userId = userAccountInfo.getUserId();
                            String platform = userAccountInfo.getPlatform();

                            j.or(k -> k.eq("platform", platform)
                                    .eq("user_id", userId));
                        }
                    })
            );



            statUserAccountQueryWrapper.groupBy("stat_time");
            statUserAccountQueryWrapper.orderByAsc("stat_time");
            List<UserA> dataList = statUserAccountService.list(statUserAccountQueryWrapper);

最终的SQL

SELECT
	stat_time,
	sum( works ) AS works,
	sum( rtt_count ) AS rtt_count,
	sum( comment_count ) AS comment_count,
	sum( like_count ) AS like_count,
	sum( fans ) AS fans 
FROM
	stat_user_account 
WHERE
	(
		stat_time BETWEEN ? 
		AND ? 
		AND (((
					platform = ? 
					AND user_id = ?) 
				OR (
					platform = ? 
				AND user_id = ?)))) 
GROUP BY
	stat_time 
ORDER BY
	stat_time ASC