自动依据你的数据库生成SQL练习题及答案，宝藏软件鉴赏

package com.madou.dbtool.relationship.panel.exercise.question;

import com.madou.common.annotation.HideClass;
import com.madou.dbtool.constans.Constans;
import com.madou.dbtool.relationship.common.BasePanelParm;
import com.madou.dbtool.relationship.panel.exercise.BaseExercise;
import com.madou.dbtool.relationship.panel.exercise.pojo.OutDegree;
import com.madou.dbtool.relationship.panel.exercise.pojo.QuestionTable;
import com.madou.dbtool.relationship.panel.exercise.pojo.ReferenceAnswer;
import com.madou.dbtool.relationship.panel.exercise.pojo.SqlQuestion;
import com.madou.dbtool.relationship.pojo.FieldInfo;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 有多对多的3张表，生成
 */
@HideClass
public class Case2Question extends BaseExercise {

    @Override
    public String name() {
        return "1对多-统计练习题-CASE百分位";
    }

    @Override
    public List<String> lables() {
        return Arrays.asList("case when then","1:N","left join","sum","count","min","max");
    }

    @Override
    public String category() {
        return "1对多";
    }

    @Override
    public int needTableCount() {
        return 1;
    }

    // 生成问题
    @Override
    public SqlQuestion generatorQuestion(BasePanelParm basePanelParm, Map<String,List<Map<String, Object>>> datas, QuestionTable questionTable) {
        List<OutDegree> outs = questionTable.getOut();
        int size = questionTable.getOut().size();
        if(size>1){
            int index1 = Constans.RANDOM.nextInt(outs.size());
            int index2 = Constans.RANDOM.nextInt(outs.size());
            while (index2==index1){
                index2 = Constans.RANDOM.nextInt(outs.size());
            }
            OutDegree mainTable = outs.get(index1);// 结果信息表
            OutDegree resuTable = outs.get(index2);// 结果信息表
            try {
                    FieldInfo fieldInfo = chooseNumberField(questionTable.getFields());
                    if (fieldInfo == null) {
                        return null;
                    }
                    int num = 100;
                    try {
                        // 判断数据是否满足生成实体的条件，如果满足则生成，不满足则跳过
                        String sql = String.format("select max(%s) as count from %s", fieldInfo.getName(), questionTable.getTableInfo().getName());
                        List<Map<String, Object>> queryResult = getDataLimit(basePanelParm, datas, sql, 1);
                        if (queryResult != null && queryResult.size() > 0) {
                            Object count = queryResult.get(0).get("count");
                            if (count != null) {
                                num = Double.valueOf("" + queryResult.get(0).get("count")).intValue();
                            } else {
                                return null;
                            }
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    int nums[] = new int[]{0,num/4,num/2,num*3/4,num};
                    if (fieldInfo != null) {
                        String resuDesc = getTableDesc(resuTable.getQuestionTable().getTableInfo());
                        SqlQuestion sqlQuestion = new SqlQuestion();
                        sqlQuestion.setName(getQuestionTitle(fieldInfo, nums, resuDesc));
                        sqlQuestion.setThinking(getThinking());
                        sqlQuestion.addReferenceAnswer(getAnswer(questionTable,fieldInfo,resuTable,nums));
                        sqlQuestion.setOutColumn(getOutColumn(resuTable,nums));
                        return sqlQuestion;
                    }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 生成结果要输出的字段说明
     * @param resuTable
     * @return
     */
    public Map<String,String> getOutColumn(OutDegree resuTable,int[] nums){
        Map<String,String> outColumn = new HashMap<>();
        outColumn.put("section1","[0,"+nums[1]+"]段");
        outColumn.put("section2","["+nums[1]+","+nums[2]+"]段");
        outColumn.put("section3","["+nums[2]+","+nums[3]+"]段");
        outColumn.put("section4","["+nums[3]+","+nums[4]+"]段");
        for (FieldInfo field : resuTable.getQuestionTable().getFields()) {
            outColumn.put(field.getName(),getFieldDesc(field));
        }
        return outColumn;
    }

    /**
     * 生成参考答案
     * @param questionTable
     * @param fieldInfo
     * @return
     */
    public ReferenceAnswer getAnswer(QuestionTable questionTable, FieldInfo fieldInfo ,OutDegree resuTable,int[] nums){
        String sql = "select c.*, b.section1, b.section2, b.section3, b.section4\n" +
                "from %s c\n" +
                "    left join (\n" +
                "        select %s\n" +
                "            , sum(case \n" +
                "                when %s >= 0\n" +
                "                    and %3$s < %s\n" +
                "                then 1\n" +
                "                else 0\n" +
                "            end) as section1\n" +
                "            , sum(case \n" +
                "                when %3$s >= %4$s\n" +
                "                    and %3$s < %s\n" +
                "                then 1\n" +
                "                else 0\n" +
                "            end) as section2\n" +
                "            , sum(case \n" +
                "                when %3$s >= %5$s\n" +
                "                    and %3$s < %s\n" +
                "                then 1\n" +
                "                else 0\n" +
                "            end) as section3\n" +
                "            , sum(case \n" +
                "                when %3$s >= %6$s\n" +
                "                    and %3$s <= %s\n" +
                "                then 1\n" +
                "                else 0\n" +
                "            end) as section4\n" +
                "        from %s\n" +
                "        group by %2$s\n" +
                "    ) b\n" +
                "    on c.id = b.%2$s";
        return new ReferenceAnswer(String.format(sql,
                resuTable.getQuestionTable().getTableInfo().getName(),
                resuTable.getFieldInfo().getName(),
                fieldInfo.getName(),
                nums[1],
                nums[2],
                nums[3],
                nums[4],
                questionTable.getTableInfo().getName()
         ));
    }

    /**
     * 生成问题的标题
     * @param resuDesc
     * @return
     */
    public String getQuestionTitle(FieldInfo fieldInfo, int[] nums,String resuDesc){
        return String.format("统计每个%s的各%s段人数[0,%s]、[%3$s,%s]、[%4$s,%s]、[%5$s,%s], 分别取别名为section1、section2、section3、section4.并显示%1$s基本信息",
                resuDesc,
                getFieldDesc(fieldInfo),
                nums[1],
                nums[2],
                nums[3],
                nums[4]);
    }

    /**
     * 生成问题思路
     * @return
     */
    public String getThinking(){
        return String.format("group by 以后的查询结果无法使用别名，所以不要想着先单表 group by 计算出结果再从第二张表里添上课程信息，而应该先将两张表 join 在一起得到所有想要的属性再对这张总表进行统计计算.");
    }

}