<select id="getToallocatedBusinessNum" resultType="Integer"> SELECT COUNT(DISTINCT(id)) AS toallocatedBusinessNum FROM `tb_business` WHERE id NOT IN ( SELECT assign_id FROM `tb_assign_record` WHERE create_time BETWEEN #{startTime} AND #{endTime} AND latest = 1 AND `type` = 1 ) AND create_time BETWEEN #{startTime} AND #{endTime} </select>写成外连接

可以将原先的子查询改写成一个左外连接，如下所示：

<select id="getToallocatedBusinessNum" resultType="Integer">
  SELECT COUNT(DISTINCT b.id) AS toallocatedBusinessNum
  FROM `tb_business` b
  LEFT JOIN (
    SELECT assign_id
    FROM `tb_assign_record`
    WHERE create_time BETWEEN #{startTime} AND #{endTime}
      AND latest = 1
      AND `type` = 1
  ) ar ON b.id = ar.assign_id
  WHERE ar.assign_id IS NULL
    AND b.create_time BETWEEN #{startTime} AND #{endTime}
</select>

这里使用了一个左外连接，将 `tb_business` 表和子查询的结果集进行连接，连接条件是 `tb_business.id = tb_assign_record.assign_id`，然后在 `WHERE` 子句中过滤掉 `tb_assign_record.assign_id IS NULL` 的记录，即未分配的业务。最后统计 `DISTINCT` 的业务数即可。