56,677
社区成员
发帖
与我相关
我的任务
分享
problems CREATE TABLE `problems` (
`problem_id` bigint(11) NOT NULL auto_increment COMMENT '问题表 ID',
`problem_type` int(1) NOT NULL COMMENT '问题类型 1.理论题,2实践题',
`problem_title` varchar(200) NOT NULL COMMENT '问题',
`problem_option_a` varchar(100) default NULL COMMENT 'A 选项',
`problem_option_b` varchar(100) default NULL COMMENT 'B 选项',
`problem_option_c` varchar(100) default NULL COMMENT 'C 选项',
`problem_option_d` varchar(100) default NULL COMMENT 'D 选项',
`correct_answer` int(1) default '0' COMMENT '正确选项 1.A,2.B,3.C,4.D',
`problem_is_main` int(1) default '1' COMMENT '是否主要問題 1不是 2 是',
`problem_image` varchar(200) default NULL COMMENT '问题图片路径',
`problem_remark` text COMMENT '描述',
PRIMARY KEY (`problem_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=gbk
select p.* FROM problems p,
(
SELECT DISTINCT problem_id
FROM
(
SELECT problem_id,
1 AS ord
FROM (SELECT problem_id
FROM problems
WHERE problem_type = 1
AND problem_is_main = 1
ORDER BY rand()
LIMIT 2) x1
UNION ALL
SELECT problem_id,
1 AS ord
FROM (SELECT problem_id
FROM problems
WHERE problem_type = 2
AND problem_is_main = 1
ORDER BY rand()
LIMIT 2) x2
UNION ALL
SELECT problem_id,
1 AS ord
FROM (SELECT problem_id
FROM problems
WHERE problem_type = 3
AND problem_is_main = 1
ORDER BY rand()
LIMIT 2) x3
UNION ALL
SELECT problem_id,
ord
FROM (SELECT problem_id,
rand() + 2 AS ord
FROM problems
ORDER BY ord
LIMIT 30) x4
ORDER BY ord
) x
ORDER BY ord
LIMIT 30
) sub
where p.problem_id = sub.problem_id