查询：LEFT JOIN UNION的一对三 一对多查询

DTO：

/**
* 员工考试记录表
* @author lichao sunlightcs@gmail.com
* @since 3.0 2021-04-09
*/
@Data
@ApiModel(value = "员工考试记录表")
public class TabrExamRecordCompleteDTO implements Serializable {
    private static final long serialVersionUID = 1L;
    @ApiModelProperty(value = "主键")
    private Long id;
    @ApiModelProperty(value = "考试名称")
    private String examName;
    @ApiModelProperty(value = "考试表id")
    private Long examId;
    @ApiModelProperty(value = "考生id")
    private Long creator;
    @ApiModelProperty(value = "考生姓名")
    private String creatorName;
    @ApiModelProperty(value = "报名时间")
    private Date createDate;
    @ApiModelProperty(value = "是否完成考试 0-未完成 1-已完成")
    private Integer completeFlag;
    @ApiModelProperty(value = "考试类型")
    private Integer examType;
    @ApiModelProperty(value = "考试开始时间")
    private Date startTime;
    @ApiModelProperty(value = "考试结束时间")
    private Date endTime;
    @ApiModelProperty(value = "考试耗时")
    private Integer estimateTime;
    @ApiModelProperty(value = "考试成绩")
    private String score;
}

Controller：

@GetMapping("getCompleteRecord")
    @ApiOperation("考生获取自己已完成的考试")
    @LogOperation("考生获取自己已完成的考试")
    @ApiImplicitParams({
            @ApiImplicitParam(name = Constant.PAGE, value = "当前页码，从1开始", paramType = "query", required = true, dataType="int") ,
            @ApiImplicitParam(name = Constant.LIMIT, value = "每页显示记录数", paramType = "query",required = true, dataType="int") ,
            @ApiImplicitParam(name = Constant.ORDER_FIELD, value = "排序字段", paramType = "query", dataType="String") ,
            @ApiImplicitParam(name = Constant.ORDER, value = "排序方式，可选值(asc、desc)", paramType = "query", dataType="String"),
            @ApiImplicitParam(name = "examName", value = "根据考试名称模糊搜索", paramType = "query", dataType="String"),
            @ApiImplicitParam(name = "examType", value = "根据考试类型检索,0-业务考试,1-桌面推演,2-体能测试", paramType = "query", dataType="int")
    })
    public Result<PageData<TabrExamRecordCompleteDTO>> getCompleteRecord(@ApiIgnore @RequestParam Map<String, Object> params) {
        //题目的分页数据
        PageData<TabrExamRecordCompleteDTO> page = tabrExamRecordService.getCompleteRecord(params);
        return new Result<PageData<TabrExamRecordCompleteDTO>>().ok(page);
    }

Service：

PageData<TabrExamRecordCompleteDTO> getCompleteRecord(Map<String, Object> params);

Impl：

 @Override
    public PageData<TabrExamRecordCompleteDTO> getCompleteRecord(Map<String, Object> params) {
        //分页
        Page<TabrExamRecordCompleteDTO> page1 =new Page<>(Long.parseLong(params.get("page").toString()),Long.parseLong(params.get("limit").toString()));
        //用户信息
        UserDetail user = SecurityUser.getUser();
        //模糊搜索
        String examName = null;
        if (params.get("examName") != null) {
            examName = params.get("examName").toString();
        }
        //考试类型检索
        Integer examType = null;
        if (params.get("examType") != null) {
            examType = Convert.toInt(params.get("examType"));
        }
        //查询
        IPage<TabrExamRecordCompleteDTO> page = baseDao.selectCompleteRecord(
                page1,
                user.getId(),
                examName,
                examType
        );
        return getPageData(page, TabrExamRecordCompleteDTO.class);
    }

Dao：

/**
     * 获取考生已完成的考试
     * @author WHN
     * @date 2021/4/16 10:44
     */
    IPage<TabrExamRecordCompleteDTO> selectCompleteRecord(Page<TabrExamRecordCompleteDTO> page1, Long userid, String examName,Integer examType);

xml：

<select id="selectCompleteRecord" resultType="io.renren.dto.TabrExamRecordCompleteDTO">
            <if test="param4 == null or param4 == 0">
                select r.id,
                r.exam_name,
                r.exam_id,
                r.creator,
                r.complete_flag,
                r.exam_type,
                u.real_name creator_name,
                e.start_time,
                e.end_time,
                e.estimate_time,
                (select group_concat(b.score) from tabr_exam_record_busi b where b.exam_record_id = r.id) score,
                (select group_concat(b.create_date) from tabr_exam_record_busi b where b.exam_record_id = r.id) create_date
                from tabr_exam_record AS r
                LEFT JOIN sys_user u on r.creator = u.id
                LEFT JOIN tabr_exam e on r.exam_id = e.id
                where r.creator = #{userid}
                and r.complete_flag = 1
                and r.exam_type = 0
                <if test="param3 != null">
                    and r.exam_name like "%"#{examName}"%"
                </if>
            </if>
            <if test="param4 == null">
                UNION
            </if>
            <if test="param4 == null or param4 == 1">
                select r.id,
                r.exam_name,
                r.exam_id,
                r.creator,
                r.complete_flag,
                r.exam_type,
                u.real_name creator_name,
                e.start_time,
                e.end_time,
                e.estimate_time,
                (select group_concat(p.score) from tabr_exam_record_plot p where p.exam_record_id = r.id) score,
                (select group_concat(p.create_date) from tabr_exam_record_plot p where p.exam_record_id = r.id) create_date
                from tabr_exam_record AS r
                LEFT JOIN sys_user u on r.creator = u.id
                LEFT JOIN tabr_exam e on r.exam_id = e.id
                where r.creator = #{userid}
                and r.complete_flag = 1
                and r.exam_type = 1
                <if test="param3 != null">
                    and r.exam_name like "%"#{examName}"%"
                </if>
            </if>
            <if test="param4 == null">
                UNION
            </if>
            <if test="param4 == null or param4 == 2">
                select r.id,
                r.exam_name,
                r.exam_id,
                r.creator,
                r.complete_flag,
                r.exam_type,
                u.real_name creator_name,
                e.start_time,
                e.end_time,
                e.estimate_time,
                (select group_concat(h.score) from tabr_physical_exam_score h where h.exam_record_id = r.id) score,
                r.create_date create_date
                from tabr_exam_record AS r
                LEFT JOIN sys_user u on r.creator = u.id
                LEFT JOIN tabr_exam e on r.exam_id = e.id
                where r.creator = #{userid}
                and r.complete_flag = 1
                and r.exam_type = 2
                <if test="param3 != null">
                    and r.exam_name like "%"#{examName}"%"
                </if>
            </if>
    </select>

Mysql表展示：

主表：tabr_exam_record，主表的id字段与其他三个表的exam_record_id字段关联

从表1：tabr_exam_record_busi

从表2：tabr_exam_record_plot

从表3：tabr_physical_exam_score

SQL测试：left join的sys_user表是用户信息表，对应主从表中的creator字段；left join的tabr_exam表是主表tabr_exam_record关联的上级表。

select 
r.id, r.exam_name, r.exam_id, r.creator, r.complete_flag, r.exam_type, u.real_name creator_name, e.start_time, e.end_time, e.estimate_time,
(select group_concat(b.score) from tabr_exam_record_busi b where b.exam_record_id = r.id) score,
(select group_concat(b.create_date) from tabr_exam_record_busi b where b.exam_record_id = r.id) create_date
                                                                 from tabr_exam_record AS r
                                                                 LEFT JOIN sys_user u on r.creator = u.id
                                                                 LEFT JOIN tabr_exam e on r.exam_id = e.id
                                                                 where r.creator = 1357866375569235970 
                                                                 and r.complete_flag = 1
                                                                 and r.exam_type = 0
                                                                 UNION
select 
r.id, r.exam_name, r.exam_id, r.creator, r.complete_flag, r.exam_type, u.real_name creator_name, e.start_time, e.end_time, e.estimate_time,
(select group_concat(b.score) from tabr_exam_record_plot b where b.exam_record_id = r.id) score,
(select group_concat(b.create_date) from tabr_exam_record_plot b where b.exam_record_id = r.id) create_date
                                                                 from tabr_exam_record AS r
                                                                 LEFT JOIN sys_user u on r.creator = u.id
                                                                 LEFT JOIN tabr_exam e on r.exam_id = e.id
                                                                 where r.creator = 1357866375569235970 
                                                                 and r.complete_flag = 1 
                                                                 and r.exam_type = 1 
                                                                 UNION
select 
r.id, r.exam_name, r.exam_id, r.creator, r.complete_flag, r.exam_type, u.real_name creator_name, e.start_time, e.end_time, e.estimate_time,
(select group_concat(b.score) from tabr_physical_exam_score b where b.exam_record_id = r.id) score,
r.create_date create_date
                                                                 from tabr_exam_record AS r
                                                                 LEFT JOIN sys_user u on r.creator = u.id
                                                                 LEFT JOIN tabr_exam e on r.exam_id = e.id
                                                                 where r.creator = 1357866375569235970 
                                                                 and r.complete_flag = 1
                                                                 and r.exam_type = 2

Swagger接口测试展示：

代码中用到的两个分页类：

package com.baomidou.mybatisplus.extension.plugins.pagination;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.metadata.OrderItem;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.function.Predicate;
import org.jetbrains.annotations.Nullable;
public class Page<T> implements IPage<T> {
    private static final long serialVersionUID = 8545996863226528798L;
    protected List<T> records;
    protected long total;
    protected long size;
    protected long current;
    protected List<OrderItem> orders;
    protected boolean optimizeCountSql;
    protected boolean isSearchCount;
    protected boolean hitCount;
    public Page() {
        this.records = Collections.emptyList();
        this.total = 0L;
        this.size = 10L;
        this.current = 1L;
        this.orders = new ArrayList();
        this.optimizeCountSql = true;
        this.isSearchCount = true;
        this.hitCount = false;
    }
    public Page(long current, long size) {
        this(current, size, 0L);
    }
    public Page(long current, long size, long total) {
        this(current, size, total, true);
    }
    public Page(long current, long size, boolean isSearchCount) {
        this(current, size, 0L, isSearchCount);
    }
    public Page(long current, long size, long total, boolean isSearchCount) {
        this.records = Collections.emptyList();
        this.total = 0L;
        this.size = 10L;
        this.current = 1L;
        this.orders = new ArrayList();
        this.optimizeCountSql = true;
        this.isSearchCount = true;
        this.hitCount = false;
        if (current > 1L) {
            this.current = current;
        }
        this.size = size;
        this.total = total;
        this.isSearchCount = isSearchCount;
    }
    public boolean hasPrevious() {
        return this.current > 1L;
    }
    public boolean hasNext() {
        return this.current < this.getPages();
    }
    public List<T> getRecords() {
        return this.records;
    }
    public Page<T> setRecords(List<T> records) {
        this.records = records;
        return this;
    }
    public long getTotal() {
        return this.total;
    }
    public Page<T> setTotal(long total) {
        this.total = total;
        return this;
    }
    public long getSize() {
        return this.size;
    }
    public Page<T> setSize(long size) {
        this.size = size;
        return this;
    }
    public long getCurrent() {
        return this.current;
    }
    public Page<T> setCurrent(long current) {
        this.current = current;
        return this;
    }
    /** @deprecated */
    @Deprecated
    @Nullable
    public String[] ascs() {
        return CollectionUtils.isNotEmpty(this.orders) ? this.mapOrderToArray(OrderItem::isAsc) : null;
    }
    private String[] mapOrderToArray(Predicate<OrderItem> filter) {
        List<String> columns = new ArrayList(this.orders.size());
        this.orders.forEach((i) -> {
            if (filter.test(i)) {
                columns.add(i.getColumn());
            }
        });
        return (String[])columns.toArray(new String[0]);
    }
    private void removeOrder(Predicate<OrderItem> filter) {
        for(int i = this.orders.size() - 1; i >= 0; --i) {
            if (filter.test(this.orders.get(i))) {
                this.orders.remove(i);
            }
        }
    }
    public Page<T> addOrder(OrderItem... items) {
        this.orders.addAll(Arrays.asList(items));
        return this;
    }
    public Page<T> addOrder(List<OrderItem> items) {
        this.orders.addAll(items);
        return this;
    }
    /** @deprecated */
    @Deprecated
    public Page<T> setAscs(List<String> ascs) {
        return CollectionUtils.isNotEmpty(ascs) ? this.setAsc((String[])ascs.toArray(new String[0])) : this;
    }
    /** @deprecated */
    @Deprecated
    public Page<T> setAsc(String... ascs) {
        this.removeOrder(OrderItem::isAsc);
        String[] var2 = ascs;
        int var3 = ascs.length;
        for(int var4 = 0; var4 < var3; ++var4) {
            String s = var2[var4];
            this.addOrder(OrderItem.asc(s));
        }
        return this;
    }
    /** @deprecated */
    @Deprecated
    public String[] descs() {
        return this.mapOrderToArray((i) -> {
            return !i.isAsc();
        });
    }
    /** @deprecated */
    @Deprecated
    public Page<T> setDescs(List<String> descs) {
        if (CollectionUtils.isNotEmpty(descs)) {
            this.removeOrder((item) -> {
                return !item.isAsc();
            });
            Iterator var2 = descs.iterator();
            while(var2.hasNext()) {
                String s = (String)var2.next();
                this.addOrder(OrderItem.desc(s));
            }
        }
        return this;
    }
    /** @deprecated */
    @Deprecated
    public Page<T> setDesc(String... descs) {
        this.setDescs(Arrays.asList(descs));
        return this;
    }
    public List<OrderItem> orders() {
        return this.getOrders();
    }
    public List<OrderItem> getOrders() {
        return this.orders;
    }
    public void setOrders(List<OrderItem> orders) {
        this.orders = orders;
    }
    public boolean optimizeCountSql() {
        return this.optimizeCountSql;
    }
    public boolean isOptimizeCountSql() {
        return this.optimizeCountSql();
    }
    public boolean isSearchCount() {
        return this.total < 0L ? false : this.isSearchCount;
    }
    public Page<T> setSearchCount(boolean isSearchCount) {
        this.isSearchCount = isSearchCount;
        return this;
    }
    public Page<T> setOptimizeCountSql(boolean optimizeCountSql) {
        this.optimizeCountSql = optimizeCountSql;
        return this;
    }
    public void hitCount(boolean hit) {
        this.hitCount = hit;
    }
    public void setHitCount(boolean hit) {
        this.hitCount = hit;
    }
    public boolean isHitCount() {
        return this.hitCount;
    }
}

package com.baomidou.mybatisplus.core.metadata;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import java.io.Serializable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;
public interface IPage<T> extends Serializable {
    /** @deprecated */
    @Deprecated
    default String[] descs() {
        return null;
    }
    /** @deprecated */
    @Deprecated
    default String[] ascs() {
        return null;
    }
    List<OrderItem> orders();
    default Map<Object, Object> condition() {
        return null;
    }
    default boolean optimizeCountSql() {
        return true;
    }
    default boolean isSearchCount() {
        return true;
    }
    default long offset() {
        return this.getCurrent() > 0L ? (this.getCurrent() - 1L) * this.getSize() : 0L;
    }
    default long getPages() {
        if (this.getSize() == 0L) {
            return 0L;
        } else {
            long pages = this.getTotal() / this.getSize();
            if (this.getTotal() % this.getSize() != 0L) {
                ++pages;
            }
            return pages;
        }
    }
    default IPage<T> setPages(long pages) {
        return this;
    }
    default void hitCount(boolean hit) {
    }
    default boolean isHitCount() {
        return false;
    }
    List<T> getRecords();
    IPage<T> setRecords(List<T> records);
    long getTotal();
    IPage<T> setTotal(long total);
    long getSize();
    IPage<T> setSize(long size);
    long getCurrent();
    IPage<T> setCurrent(long current);
    default <R> IPage<R> convert(Function<? super T, ? extends R> mapper) {
        List<R> collect = (List)this.getRecords().stream().map(mapper).collect(Collectors.toList());
        return this.setRecords(collect);
    }
    default String cacheKey() {
        StringBuilder key = new StringBuilder();
        key.append(this.offset()).append(":").append(this.getSize());
        List<OrderItem> orders = this.orders();
        if (CollectionUtils.isNotEmpty(orders)) {
            Iterator var3 = orders.iterator();
            while(var3.hasNext()) {
                OrderItem item = (OrderItem)var3.next();
                key.append(":").append(item.getColumn()).append(":").append(item.isAsc());
            }
        }
        return key.toString();
    }
}