z4gyM7dz

1-- Сформировать тест из вопросов так, чтобы простые и сложные вопросы входили в него в соответствии с заданным процентным соотношением
2drop table if exists Question;
3create table if not exists Question (
4	question varchar(45),
5	answer1 varchar(45),
6    answer2 varchar(45),
7    answer3 varchar(45),
8    complexity int,
9    primary key (question)
10);
11
12insert into Question value ("question1", "answer1_1", "answer2_1", "answer3_1", 1);
13insert into Question value ("question2", "answer1_2", "answer2_2", "answer3_2", 1);
14insert into Question value ("question3", "answer1_3", "answer2_3", "answer3_3", 3);
15insert into Question value ("question4", "answer1_4", "answer2_4", "answer3_4", 1);
16insert into Question value ("question5", "answer1_5", "answer2_5", "answer3_5", 2);
17insert into Question value ("question6", "answer1_6", "answer2_6", "answer3_6", 3);
18insert into Question value ("question7", "answer1_7", "answer2_7", "answer3_7", 1);
19insert into Question value ("question8", "answer1_8", "answer2_8", "answer3_8", 3);
20insert into Question value ("question9", "answer1_9", "answer2_9", "answer3_9", 3);
21insert into Question value ("question10", "answer1_10", "answer2_10", "answer3_10", 2);
22insert into Question value ("question11", "answer1_11", "answer2_11", "answer3_11", 1);
23insert into Question value ("question12", "answer1_12", "answer2_12", "answer3_12", 2);
24insert into Question value ("question13", "answer1_13", "answer2_13", "answer3_13", 3);
25insert into Question value ("question14", "answer1_14", "answer2_14", "answer3_14", 1);
26
27drop procedure if exists getTest;
28DELIMITER //
29CREATE PROCEDURE getTest(easyTasksPercent int, hardTasksPercent int, tasksOnTest int)
30BEGIN
31    declare done integer default 0;
32    
33    declare question varchar(45);
34    declare answer1 varchar(45);
35    declare answer2 varchar(45);
36    declare answer3 varchar(45);
37    declare complexity int;
38    
39    declare easyTasksMaxCount int default 0;
40    declare hardTasksMaxCount int default 0;
41    declare mediumTasksMaxCount int default 0;
42    declare allTasksMaxCount int default tasksOnTest;
43    
44	declare questionCursor Cursor for select Question.question, Question.answer1, Question.answer2, Question.answer3, Question.complexity from Question;
45    
46    declare continue handler for sqlstate '02000' SET done=1;
47    
48    drop table if exists DoneTest; -- Создаём таблицу, в которую будем складывать подходящие вопросы
49	create table if not exists DoneTest(
50		question varchar(45),
51		answer1 varchar(45),
52		answer2 varchar(45),
53		answer3 varchar(45),
54		complexity int,
55		id int not null auto_increment,
56		primary key(id)
57	);
58    
59    -- Считаем количество нужных лёгких, тяжелых и средник вопросов, исходя из входных данных
60    set easyTasksMaxCount = tasksOnTest * (easyTasksPercent / 100);
61    set hardTasksMaxCount = tasksOnTest * (hardTasksPercent / 100);
62    set mediumTasksMaxCount = tasksOnTest - easyTasksMaxCount - hardTasksMaxCount;
63    
64    open questionCursor;
65
66    label1: while done = 0 do
67		fetch questionCursor into question, answer1, answer2, answer3, complexity;
68        
69        if (easyTasksMaxCount > 0 and complexity = 1) then -- Если нам еще нужны простые вопросы и текущий вопрос простой, то добавляем
70			insert into DoneTest(question, answer1, answer2, answer3, complexity) value(question, answer1, answer2, answer3, complexity);
71            set easyTasksMaxCount = easyTasksMaxCount - 1;
72            set allTasksMaxCount = allTasksMaxCount - 1;
73		end if;
74        
75		if (hardTasksMaxCount > 0 and complexity = 3) then -- Если нам еще нужны сложные вопросы и текущий вопрос сложный, то добавляем
76			insert into DoneTest(question, answer1, answer2, answer3, complexity) value(question, answer1, answer2, answer3, complexity);
77            set hardTasksMaxCount = hardTasksMaxCount - 1;
78            set allTasksMaxCount = allTasksMaxCount - 1;
79		end if;		
80        
81        if (mediumTasksMaxCount > 0 and complexity = 2) then -- Если нам еще нужны средние вопросы и текущий вопрос средний, то добавляем
82			insert into DoneTest(question, answer1, answer2, answer3, complexity) value(question, answer1, answer2, answer3, complexity);
83            set mediumTasksMaxCount = mediumTasksMaxCount - 1;
84            set allTasksMaxCount = allTasksMaxCount - 1;
85		end if;
86        
87        if(allTasksMaxCount = 0) then -- Если вопросы нам больше не нужны, то выходим из цикла, не дожидаясь пробежки по всем элементам
88			leave label1;
89		end if;
90	end while;
91    close questionCursor;
92END//
93DELIMITER ;
94
95call getTest(40, 40, 10);
96select * from DoneTest;