随机答题模块，MySQL根据一定概率随机获取数据

DDL+DML

-- ----------------------------
-- Table structure for t_question
-- ----------------------------
CREATE TABLE `t_question` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question` varchar(100) DEFAULT NULL COMMENT '问题',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of t_question
-- ----------------------------
INSERT INTO `t_question` VALUES (1, '世界上最高的山峰是？');
INSERT INTO `t_question` VALUES (2, 'mysql的底层结构？');
INSERT INTO `t_question` VALUES (3, 'mysql左连接的结果？');
INSERT INTO `t_question` VALUES (4, 'java的特点是？');
INSERT INTO `t_question` VALUES (5, 'java的四大特征是？');
INSERT INTO `t_question` VALUES (6, '&和&&的区别？ ？');
INSERT INTO `t_question` VALUES (7, '解释内存中的栈(stack)、堆(heap)和方法区(method area)的用法？');
INSERT INTO `t_question` VALUES (8, 'Math.round(11.5) 等于多少？Math.round(-11.5)等于多少？');
INSERT INTO `t_question` VALUES (9, 'switch 是否能作用在byte 上，是否能作用在long 上，是否能作用在String上？');
INSERT INTO `t_question` VALUES (10, '用最有效率的方法计算2乘以8？');
INSERT INTO `t_question` VALUES (11, '数组有没有length()方法？String有没有length()方法？');
INSERT INTO `t_question` VALUES (12, '在Java中，如何跳出当前的多重嵌套循环？');
INSERT INTO `t_question` VALUES (13, '构造器（constructor）是否可被重写（override）？');
INSERT INTO `t_question` VALUES (14, '两个对象值相同(x.equals(y) == true)，但却可有不同的hash code，这句话对不对？');
INSERT INTO `t_question` VALUES (15, '是否可以继承String类？');
INSERT INTO `t_question` VALUES (16, 'String和StringBuilder、StringBuffer的区别？');

-- ----------------------------
-- Table structure for t_answer
-- ----------------------------
drop table if exists t_answer;
CREATE TABLE `t_answer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bingo_num` int(10) DEFAULT NULL COMMENT '答对次数',
  `error_num` int(10) DEFAULT NULL COMMENT '答错次数',
  `user_id` int(10) DEFAULT NULL COMMENT '用户id',
  `question_id` int(11) DEFAULT NULL COMMENT '问题id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of t_answer
-- ----------------------------
INSERT INTO `t_answer` VALUES (1, 10, 0, 1, 1);
INSERT INTO `t_answer` VALUES (2, 5, 1, 1, 2);
INSERT INTO `t_answer` VALUES (3, 20, 0, 1, 3);
INSERT INTO `t_answer` VALUES (4, 20, 0, 1, 4);
INSERT INTO `t_answer` VALUES (5, 30, 0, 1, 5);
INSERT INTO `t_answer` VALUES (6, 3, 0, 1, 6);
INSERT INTO `t_answer` VALUES (7, 2, 5, 1, 7);
INSERT INTO `t_answer` VALUES (8, 8, 2, 1, 8);
INSERT INTO `t_answer` VALUES (9, 2, 1, 1, 9);
INSERT INTO `t_answer` VALUES (10, 5, 2, 1, 10);
INSERT INTO `t_answer` VALUES (11, 10, 0, 1, 11);
INSERT INTO `t_answer` VALUES (12, 5, 0, 1, 12);
INSERT INTO `t_answer` VALUES (13, 0, 0, 1, 13);
INSERT INTO `t_answer` VALUES (14, 2, 0, 1, 14);
INSERT INTO `t_answer` VALUES (15, 0, 0, 1, 15);
INSERT INTO `t_answer` VALUES (16, 1, 0, 1, 16);

SQL 查询

---

-- rules
-- 一题都没有答 80%
-- 如果小于平均答题数一半 60%
-- 如果大于平均答题数一半
	-- (答错/答对+答错)次数的比例

-- 芜湖
explain
select 
	case 
		when t.perc=0 then 10*rand()
		else t.perc*rand() 
	end as rand_sort,
	t.*
from(
	SELECT
		case 
			when ans_num=0 then 80 
			when (ans_num/ans_avg)<0.6 then 60 
			else ceil((error_num/ans_num)*100)
		end as perc,
		t.*
	FROM (
		select
			-- 总答题数
			(select sum(bingo_num)+sum(error_num) from t_answer)  as total,
			-- 平均答题数
			(select floor(avg(bingo_num+error_num)) from t_answer)  as ans_avg,
			-- 该题答题数
			(bingo_num+error_num) as ans_num,
			ta.*,tq.question
		from t_answer ta
		inner join t_question tq on tq.id = ta.question_id
		where user_id=1
	) t
) t
ORDER BY rand_sort desc