在线答题应用、作业题应用设计——出题部分

需求：线上学生做作业，学生的年级、班级（年级下的小班）作业（不同的年级班级会有不同的作业题），题目做完提交作业。老师查看提交的作业的学生有多少了（答题列表），点击答题列表的一条数据查看学生的作业。

这里是前置部分：出题部分

前后端加起来代码过多，贴出来给人杂乱的感觉，这里就贴出效果图和设计应该会给没思路的朋友提供帮助

效果：

表设计：

CREATE TABLE `homework_grade` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `parent_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '父id',
  `name` varchar(20) NOT NULL COMMENT '年级阶段，例如：Y1,S1',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `grade_unique` (`parent_id`,`name`) USING BTREE COMMENT '年级不能重复索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='年级表、班级表';

CREATE TABLE `homework_subject` (
  `id` bigint(20) NOT NULL COMMENT ' 主键',
  `hg_id` bigint(20) DEFAULT NULL COMMENT '年级表中班级的id，homework_grade',
  `parent_id` bigint(20) DEFAULT NULL COMMENT '父id',
  `type` int(1) DEFAULT NULL COMMENT '作业类型，1：答题，2：选择题，3：填空题',
  `sort` varchar(4) NOT NULL COMMENT '作业顺序',
  `content_text` varchar(200) DEFAULT NULL COMMENT '作业题目文本',
  `content_url` varchar(200) DEFAULT NULL COMMENT '作业题目图片',
  `score` int(3) NOT NULL DEFAULT '0' COMMENT '作业题分数',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='作业题表';

sql语句：

// 添加年级

INSERT INTO homework_grade ( id, name, create_time ) VALUES ( ?, ?, ? )

1296721814973661185(Long), Y3(String), 2020-08-21T16:12:27.273(LocalDateTime)

// 添加班级

INSERT INTO homework_grade ( id, parent_id, name, create_time ) VALUES ( ?, ?, ?, ? )

1296722436175249409(Long), 1296721814973661185(Long), S1(String), 2020-08-21T16:14:55.379(LocalDateTime)

// 添加作业

INSERT INTO homework_subject ( id, hg_id, type, sort, content_text, score, create_time ) VALUES ( ?, ?, ?, ?, ?, ?, ? )

1296723065547341825(Long), 1296722436175249409(Long), 2(Integer), 1(String), 我是一道什么题目？(String), 10(Integer), 2020-08-21T16:17:25.433(LocalDateTime)

// 如果作业题类型是选择题，还要添加选择题选项

INSERT INTO homework_subject ( id, parent_id, sort, content_text, create_time ) VALUES ( ?, ?, ?, ?, ? )

1296723065585090561(Long), 1296723065547341825(Long), A(String), 选择题(String), 2020-08-21T16:17:25.442(LocalDateTime)

1296723065627033601(Long), 1296723065547341825(Long), B(String), 问答题(String), 2020-08-21T16:17:25.452(LocalDateTime)

// 查询题目列表

SELECT hg1.id AS y_id, hg2.id AS s_id, hs.id AS hs_id, hg2.parent_id AS s_parent_id, hg1.`name` AS y_name, hg2.`name` AS s_name, hs.parent_id AS hs_parent_id, hs.type, hs.sort, hs.content_text, hs.content_url, hs.score, hs.create_time FROM homework_grade hg1 LEFT JOIN homework_grade hg2 ON hg1.id = hg2.parent_id INNER JOIN homework_subject hs ON hg2.id = hs.hg_id WHERE (hg1.`name` LIKE ? AND hg2.`name` LIKE ?) ORDER BY hg1.`name` ASC , hg2.`name` ASC , hs.sort ASC LIMIT ?,?

%Y1%(String), %S1%(String), 0(Long), 15(Long)

// 遍历题目列表，查询题目类型是选择题的选项

SELECT id, parent_id, sort, content_text, content_url FROM homework_subject WHERE (parent_id = ?) ORDER BY sort ASC

1296718039231840258(Long)