1、需求场景

业务系统中需要进行多张数据表的合并操作，使用union/union all合并数据，但是不同数据表的字段不可能一致，但是又要求可以任意合并数据，这时直接union/union all操作会报错上下表字段不一致，针对这种需求首先需要做字段补位，将N张表的字段数量处理为一致，所以需要设计一个二维矩阵补位的算法。

2、sql示例

情景1：直接合并报错，字段数量不一致
The used SELECT statements have a different number of columns

SELECT t1.`className`,t1.`createTime`,t1.`modifier` FROM `class` t1
UNION 
SELECT t2.namepaper,2.paperStatus,t2.passScore,t2.testQuestions FROM `paper` t2;

情景2：字段数量一致的情况下，不同表的字段在同列显示，不符合要求

sql.png

正确显示应为：

sql2.png

3、解决方案-矩阵补位

package com.cloud.server.common;

import com.alibaba.fastjson.JSON;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @Author: chenyaohua
 * @Date: 2021/12/16
 * @Description: 数组处理工具
 */
public class ArrayTool {

    private static final String SPLIT_SYMBOL = ",";

    /**
     * @Author: chenyaohua
     * @Date: 2021/12/16
     * @Description: 矩阵补位，标识符为“null”
     */
    public static List<String[]> makeFullArrays(List<String> rowArray){
        String[] headArray = rowArray.get(0).split(SPLIT_SYMBOL);
        List<String[]> tempList = new ArrayList<>(16);
        tempList.add(headArray);
        List<String> newHeaderList = new ArrayList<>(16);
        newHeaderList.addAll(Arrays.asList(headArray));

        for (int x = 1; x < rowArray.size(); x++) {
            String[] originRow = rowArray.get(x).split(SPLIT_SYMBOL);

            List<String> processRow = new ArrayList<>(16);
            if(x == 1){
                for (int i = 0; i < headArray.length; i++) {
                    processRow.add(null);
                }
            } else {
                for (int i = 0; i < tempList.get(tempList.size()-1).length; i++) {
                    processRow.add(null);
                }
            }

            for (int i = 0; i < headArray.length; i++) {
                for (String s : originRow) {
                    if (s.equals(headArray[i])) {
                        processRow.add(i, headArray[i]);
                        processRow.remove(processRow.size() - 1);
                    }
                }
            }

            for (String s : originRow) {
                if (!processRow.contains(s)) {
                    processRow.add(s);
                    newHeaderList.add(s);
                }
            }
            tempList.add(processRow.toArray(new String[0]));
        }
        tempList.remove(0);
        tempList.add(0,newHeaderList.toArray(new String[0]));

        List<String[]> resList = new ArrayList<>(16);
        tempList.forEach(item ->{
            List<String> rowData = new ArrayList<>(16);
            rowData.addAll(Arrays.asList(item));
            if(item.length<tempList.get(0).length){
                for (int i = item.length; i < tempList.get(0).length + tempList.get(0).length - item.length - 1; i++) {
                    rowData.add(i,null);
                }
            }
            resList.add(rowData.toArray(new String[0]));
        });
        return resList;
    }

    public static void main(String[] args) {
        String t1 = "A,B,C,D,E,F,G";
        String t2 = "B,J,W";
        String t3 = "B,C,K";
        List<String> l1 = new ArrayList<>(16);
        l1.add(t1);
        l1.add(t2);
        l1.add(t3);

        List<String[]> result = makeFullArrays(l1);
        result.forEach(item -> {
            System.out.println(JSON.toJSON(item));
        });
    }
}

4、运行结果

["A","B","C","D","E","F","G","J","W","K"]
[null,"B",null,null,null,null,null,"J","W",null]
[null,"B","C",null,null,null,null,null,null,"K"]