多表 count( ) 时应取消 count 内拼接的 as 别名

[link2fun]

复现场景， 分页去重查询 自动生成 count 语句

EasyPageResult<AllocatedUserDTO> pageResult = entityQuery.queryable(SysUser.class).asAlias(SysUser.TABLE_ALIAS)
      .leftJoin(SysDept.class, (user, dept) -> user.deptId().eq(dept.deptId())).asAlias(SysDept.TABLE_ALIAS)
      .leftJoin(SysUserRole.class, (user, dept, userRole) -> user.userId().eq(userRole.userId()))
      .leftJoin(SysRole.class, (user, dept, userRole, role) -> userRole.roleId().eq(role.roleId()))

      .where((user, dept, userRole, role) -> {
        user.delFlag().eq(UserConstants.NORMAL);
        role.roleId().eq(searchReq.getRoleId());
        user.userName().like(StrUtil.isNotBlank(searchReq.getUserName()), searchReq.getUserName());
        user.phonenumber().like(StrUtil.isNotBlank(searchReq.getPhonenumber()), searchReq.getPhonenumber());
        if (StrUtil.isNotBlank(searchReq.getParams().getDataScope())) {
          user.expression().sql(searchReq.getParams().getDataScope());
        }
      })
      .select((user, dept, userRole, role) -> {
        return new AllocatedUserDTOProxy()
          .userId().set(user.userId())
          .deptId().set(user.deptId())
          .userName().set(user.userName())
          .nickName().set(user.nickName())
          .email().set(user.email())
          .phonenumber().set(user.phonenumber())
          .status().set(user.status())
          .createTime().set(user.createTime());
      })
      .distinct()
      .toPageResult(page.getPageNum(), page.getPageSize());

生成的 count 语句为

SELECT COUNT(DISTINCT user.`user_id` AS `user_id`, user.`dept_id` AS `dept_id`, user.`user_name` AS `user_name`,
             user.`nick_name` AS `nick_name`, user.`email` AS `email`, user.`phonenumber` AS `phonenumber`,
             user.`status` AS `status`, user.`create_time` AS `create_time`)
FROM `sys_user` user
         LEFT JOIN `sys_dept` dept ON user.`dept_id` = dept.`dept_id`
         LEFT JOIN `sys_user_role` t2 ON user.`user_id` = t2.`user_id`
         LEFT JOIN `sys_role` t3 ON t2.`role_id` = t3.`role_id`
WHERE user.`del_flag` = 0
  AND t3.`role_id` = 2;

count 内部有 as 别名， SQL 执行报错

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS `user_id`, user.`dept_id` AS `dept_id`, user.`user_name` AS `user_name`, ' at line 1

解决方法 count 时不使用 as 别名

更正后的语句

SELECT COUNT(DISTINCT user.`user_id`, user.`dept_id`, user.`user_name`,
             user.`nick_name`, user.`email`, user.`phonenumber`,
             user.`status`, user.`create_time`)
FROM `sys_user` user
         LEFT JOIN `sys_dept` dept ON user.`dept_id` = dept.`dept_id`
         LEFT JOIN `sys_user_role` t2 ON user.`user_id` = t2.`user_id`
         LEFT JOIN `sys_role` t3 ON t2.`role_id` = t3.`role_id`
WHERE user.`del_flag` = 0
  AND t3.`role_id` = 2;