复杂逻辑SQL

1、分类统计文章总数

总共四张表

文章表mxg_article

文章标签中间表  mxg_article_label

标签表mxg_label

分类表mxg_category

 （1）先通过文章表:mxg_article 和 文章标签中间表:mxg_article_label 统计标签对应的合格文章数

SELECT
	m2.label_id,
	COUNT(m1.id) total
FROM
	mxg_article m1
JOIN mxg_article_label m2 ON m1.id = m2.article_id
WHERE
	m1.ispublic = 1
AND m1.`status` = 2
GROUP BY
	m2.label_id

再将字段SQL查询出来的上面的表，作为一个字表再统计查询

1、 AS 'value' 对统计的值进行赋值

2、 IFNULL(SUM(t3.total),0)，如果统计的值为空，则将后面的值进行赋值，

3、GROUP BY 依照哪个字段进行分组

SELECT t1.`name`, IFNULL(SUM(t3.total),0) AS 'value' FROM mxg_category t1 LEFT JOIN mxg_label t2 ON t1.id=t2.category_id
LEFT JOIN 
(SELECT
	m2.label_id,
	COUNT(m1.id) total
FROM
	mxg_article m1
JOIN mxg_article_label m2 ON m1.id = m2.article_id
WHERE
	m1.ispublic = 1
AND m1.`status` = 2
GROUP BY
	m2.label_id) t3 ON t2.id=t3.label_id 
GROUP BY t1.`name`

 

4、封装SQL为视图

通过 CREATE VIEW v_category_label AS

所建立的视图相当于一个表

select * from v_category_label 

CREATE VIEW v_category_label AS
SELECT t1.`name`, IFNULL(SUM(t3.total),0) AS 'value' FROM mxg_category t1 LEFT JOIN mxg_label t2 ON t1.id=t2.category_id
LEFT JOIN 
(SELECT
	m2.label_id,
	COUNT(m1.id) total
FROM
	mxg_article m1
JOIN mxg_article_label m2 ON m1.id = m2.article_id
WHERE
	m1.ispublic = 1
AND m1.`status` = 2
GROUP BY
	m2.label_id) t3 ON t2.id=t3.label_id 
GROUP BY t1.`name`

 5、mybatis引用

     mapper

    /**
     * 统计每个分类下的文章数
     * @return
     */
    List<Map<String,Object>> selectCategoryTotal();

    map.xml 

   <select id="selectCategoryTotal" resultType="map">
        SELECT `name` ,`value` FROM  v_category_label
    </select>

2、批量新增

因为使用类来调用方法，所以使用${}

主键ID新增采用分布式ID，采用mybatisPlus提供的getID的方法

ctrl+shift+n全局寻找IdWorker类

com.baomidou.mybatisplus.core.toolkit.IdWorker.getId()

   <insert id="saveArticleLabel">
        insert into mxg_article_label (id ,article_id,label_id) values
        <foreach collection="labelIds" item="item" separator=",">
            ('${@com.baomidou.mybatisplus.core.toolkit.IdWorker@getId()}',#{articleId},#{item})
        </foreach>
    </insert>

   其主要逻辑是

insert into 表名（字段一，字段二，字段三） values (值一，值二，值三)，(值一，值二，值三)

3、递归查询

1、数据库结构如下

 parentID为父ID

2、在mybatis中使用递归方式进行查询，mapper如下

实体类

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("mxg_comment")
@ApiModel(value = "Comment对象", description = "评论信息表")
public class Comment implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "主键")
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private String id;
     。。。。。。。。。。。。中间省略其他字段

    @ApiModelProperty(value = "子评论集合")
    @TableField(exist = false)
    private List<Comment> children;

}

   通过文章ID，递归查询用户评论

     /**
     * 通过文章ID
     * 递归查询文章评论
     */
     List<Comment> findByArticleId(@Param("articleId") String articleId);

3、mapper.xml如下

     （1）、确定父评论 parent_id=-1（父评论可以多个）

    <select id="findByArticleId" resultMap="commentResultMap">
      SELECT * FROM mxg_comment WHERE parent_id=-1 AND article_id=#{articleId}
    </select>

     （2）、关联一个resultMap，使用collection进行分组

       column="id"  绑定一条记录的ID并作为下一个查询条件  

        select="findByParentId"  指定一个查询方法

<resultMap id="commentResultMap" type="Comment">
        <id column="id" property="id"></id>
        <result column="parent_id" property="parentId"></result>
        <result column="user_id" property="parentId"></result>
        <result column="nick_name" property="nickName"></result>
        <result column="user_image" property="userImage"></result>
        <result column="article_id" property="articleId"></result>
        <result column="content" property="content"></result>
        <result column="create_date" property="createDate"></result>
        <collection property="children" javaType="list" ofType="Comment" column="id" select="findByParentId">
        </collection>
    </resultMap>

（3）、指定的查询方法findByParentId，并通过 resultMap="commentResultMap"绑定上一个resultMap

<!--    递归查询所有评论-->
    <select id="findByParentId" resultMap="commentResultMap">
        SELECT *FROM mxg_comment WHERE parent_id=#{id}
    </select>

 4、mybatisPlus的递归查询（表结构和上面一样，id和parentId）

1、Server定义接口

 Result queryList(SysMenuREQ req);

2、serverIml实现接口

  (1)、判断查询条件的name是否为空，查询所有的数据，并自定义排序。
（2)、通过查询到的list<对象> sysMenusList  ，进行foreach循环，查询到头部也就是parentId为-1的对象，并封装到一个新的数组sysMenusListHead 中

（3）、在将sysMenusListHead进行foreach循环，每一个将每一个对象，和之前的数组传入方法getDetailMenu 进行循环递归

（4）、通过对sysMenusList  进行递归查询，所有的id==sysMenusListHead中单个对象的parentID，循环结束，再对单个对象的children进行赋值封装，相当于所有数据都存放在了 sysMenusListHead 中

   @Override
    public Result queryList(SysMenuREQ req) {
        LambdaQueryWrapper<SysMenu> sysMenuLambdaQueryWrapper = new LambdaQueryWrapper<>();
        //判断传入参数是否为空
        if (StringUtils.isNotEmpty(req.getName())) {
            sysMenuLambdaQueryWrapper.eq(SysMenu::getName, req.getName());
        }
        //按照排序升序 asc 1到2 ，创建时间降序 2到1
        sysMenuLambdaQueryWrapper.orderByAsc(SysMenu::getSort).orderByDesc(SysMenu::getUpdateDate);
        //通过条件查询到的SysMenu的List数组sysMenusList
        List<SysMenu> sysMenusList = baseMapper.selectList(sysMenuLambdaQueryWrapper);
        List<SysMenu> sysMenusListHead = new ArrayList<>();

        /**
         * 先找到数据头
         *
         */
        for (SysMenu sys : sysMenusList
        ) {
            if (sys.getParentId().equals("-1")) {
                sysMenusListHead.add(sys);
            }

        }
        for (SysMenu headMenu : sysMenusListHead
        ) {
            getDetailMenu(sysMenusList, headMenu);

        }

        return Result.ok(sysMenusListHead);
    }

递归查询并封装到每一个

private SysMenu getDetailMenu(List<SysMenu> sysMenusList, SysMenu headMenu) {

        List<SysMenu> children = new ArrayList<>();


        for (SysMenu system : sysMenusList
        ) {
            if (system.getParentId().equals(headMenu.getId())) {
                getDetailMenu(sysMenusList, system);
                children.add(system);
            }
        }
        headMenu.setChildren(children);
        return headMenu;
    }

5、递归删除

1、创建list集合存放ids，

2、创建getByParentId方法递归查询id

3、因为采用递归，而且使用的是同一个对象，所以不用返回值，递归结束，拿到所有的Id集合

4、采用mybatisPlus中的deleteBatchIds进行统一删除

    @Override
    public Result deleteByParentId(String id) {
        List<String> ids = new ArrayList<>();
        //添加第一个ID
        ids.add(id);
        //因为是同一个对象，所以不用返回值
        //递归所有的评论ID,将ID封装到ID集合中
        this.getByParentId(ids, id);
        baseMapper.deleteBatchIds(ids);
        return Result.ok();
    }

    private void getByParentId(List<String> ids, String id) {
        LambdaQueryWrapper<Comment> commentLambdaQueryWrapper = new LambdaQueryWrapper<>();
        commentLambdaQueryWrapper.eq(Comment::getParentId, id);
        //查询这个ID是否被其他引用成父评论ID
        List<Comment> comments = baseMapper.selectList(commentLambdaQueryWrapper);
        if (CollectionUtils.isNotEmpty(comments)) {
            for (Comment co : comments
            ) {
                String id1 = co.getId();
                ids.add(id1);
                this.getByParentId(ids, id1);
            }
        }

    }

6、递归查询并使用column="{id=id,userId=replyThumbUserId}"传递多个参数，

（1）、一般默认column="id”，传递当前查询的Id信息，采用“{}”这种方式能传递多个数据库查询到的多个参数，给下一个查询语句使用。

（2）、SQL中的if语句， IF (m2.id > 0, 1, 0) AS userThumb,，这个和比目运算符用法一致

(3)、SQL中给空字段赋默认值，IFNULL(SUM(t3.total),0) AS 'value 

    <select id="listReplyByQuestionId" resultMap="listReplyByQuestionMap">
        SELECT
            m1.`id`,
            m1.`parent_id`,
            m1.`content`,
            m1.`laud`,
            m1.`question_id`,
            m1.`user_id`,
            m1.`nick_name`,
            m1.`user_image_url`,
            m1.`create_date`,
            m1.`sort`,
        IF (m2.id > 0, 1, 0) AS userThumb,
        IF (m2.id > 0, m2.user_id, 0) AS replyThumbUserId
        FROM
            tb_question_reply m1
        LEFT JOIN tb_question_reply_thumb m2 ON m2.question_reply_id = m1.id
        AND m2.user_id = #{req.userId}
        WHERE
            m1.parent_id = 0
        AND m1.question_id = #{req.questionId}
        ORDER BY
            sort DESC,
            create_date DESC
    </select>
    <resultMap id="listReplyByQuestionMap" type="QuestionReply">
        <id property="id" column="id"></id>
        <result property="parentId" column="parent_id"></result>
        <result property="content" column="content"></result>
        <result property="laud" column="laud"></result>
        <result property="questionId" column="question_id"></result>
        <result property="userId" column="user_id"></result>
        <result property="nickName" column="nick_name"></result>
        <result property="userImageUrl" column="user_image_url"></result>
        <result property="createDate" column="create_date"></result>
        <result property="userThumb" column="userThumb"></result>
        <collection property="children" column="{id=id,userId=replyThumbUserId}" javaType="list" ofType="QuestionReply" select="selectByParentId">

        </collection>

    </resultMap>
    <select id="selectByParentId" resultMap="listReplyByQuestionMap">
               SELECT
            m1.`id`,
            m1.`parent_id`,
            m1.`content`,
            m1.`laud`,
            m1.`question_id`,
            m1.`user_id`,
            m1.`nick_name`,
            m1.`user_image_url`,
            m1.`create_date`,
            m1.`sort`,

        IF (m2.id > 0, 1, 0) AS userThumb,
        IF (m2.id > 0, m2.user_id, 0) AS replyThumbUserId
        FROM
            tb_question_reply m1
        LEFT JOIN tb_question_reply_thumb m2 ON m2.question_reply_id = m1.id AND m2.user_id = #{userId}
        WHERE
            parent_id = #{id}
    </select>