#mysql
#mysql
Вопрос:
Существующая система тестирования врачей (множественный выбор) содержит таблицу врачей, таблицу тестов, таблицу вопросов и таблицу ответов (по 4 на вопрос) и, наконец, таблицу ответов, предоставленных врачом.
Схема как таковая:
doctors Таблица
id |initials|
1661 |AP
tests Таблица
id |test |released |expires
141|Example|2009-02-21|2010-03-01
test_questions Таблица (например: правильный ответ для test_question.id 3417 — это test_answers.id=5249)
id |test_id|question |test_answer_id
3417|141 |1. Example Question? |5249
3418|141 |2. Example Question? |5252
3419|141 |3. Example Question? |5254
3420|141 |4. Example Question? |5261
3421|141 |5. Example Question? |5262
3422|141 |6. Example Question? |5269
3423|141 |7. Example Question? |5273
3424|141 |8. Example Question? |5275
3425|141 |9. Example Question? |5279
3426|141 |10. Example Question?|5285
test_answers Таблица (например, усеченная)
id|test_question_id|answer
5249|3417| (d) It depends d
5248|3417| (c) depends c
5247|3417| (b) It depends b
5246|3417| (a) depends
doctor_test_answers Таблица (это ответы, выбранные врачами для каждого вопроса)
id |doctor_id|test_id|test_question_id|test_answer_id
189981|1661 |141 |3417 |5248
189982|1661 |141 |3418 |5252
189983|1661 |141 |3419 |5254
189984|1661 |141 |3420 |5261
189985|1661 |141 |3421 |5262
189986|1661 |141 |3422 |5269
189987|1661 |141 |3423 |5273
189988|1661 |141 |3424 |5275
189989|1661 |141 |3425 |5279
189990|1661 |141 |3426 |5285
Как вы можете видеть из приведенного выше, в этом случае доктор неправильно ответил на test_question_id 3417 (test_answer_id 5248 вместо 5249 и поэтому должен получить 90% в результате для теста)
Используя чистый SQL (без процедур / функций), как я могу создать запрос, который приведет к списку тестов, проведенных врачом, и их проценту для указанного теста
Желаемый набор результатов:
doctor_id|initials|test_id|test |result
1661 |AP |141 |Example|90%
В настоящее время я использую несколько запросов, сначала выбирая все тесты, написанные врачом, а затем программно просматривая ответы каждого из них, чтобы проверить результат.
Вместо этого я хотел бы сделать это в одном запросе…
Комментарии:
1. Идентификатор является ОСНОВНЫМ (например, 3417), поэтому нет смысла хранить 141 в таблице doctors_test_answers.
2. Да, я бы сделал все по-другому, если бы начал с чистого листа — но это то, с чем мне приходится работать, поскольку я вношу изменения в существующую систему 🙂
Ответ №1:
Поскольку у нас есть (избыточный) столбец test_id, мы можем также использовать его…
DROP TABLE IF EXISTS doctors_test_answers;
CREATE TABLE doctors_test_answers
(id SERIAL PRIMARY KEY
,doctor_id INT NOT NULL
,test_id INT NOT NULL
,test_question_id INT NOT NULL
,test_answer_id INT NOT NULL
);
INSERT INTO doctors_test_answers VALUES
(189981,1661,141,3417,5248),
(189982,1661,141,3418,5252),
(189983,1661,141,3419,5254),
(189984,1661,141,3420,5261),
(189985,1661,141,3421,5262),
(189986,1661,141,3422,5269),
(189987,1661,141,3423,5273),
(189988,1661,141,3424,5275),
(189989,1661,141,3425,5279),
(189990,1661,141,3426,5285);
DROP TABLE IF EXISTS test_questions;
CREATE TABLE test_questions
(id SERIAL PRIMARY KEY
,test_id INT NOT NULL
,question_no TINYINT NOT NULL
,question_text VARCHAR(100) NOT NULL
,correct_answer_id INT NOT NULL
);
INSERT INTO test_questions VALUES
(3417,141, 1,'Example Question?',5249),
(3418,141, 2,'Example Question?',5252),
(3419,141, 3,'Example Question?',5254),
(3420,141, 4,'Example Question?',5261),
(3421,141, 5,'Example Question?',5262),
(3422,141, 6,'Example Question?',5269),
(3423,141, 7,'Example Question?',5273),
(3424,141, 8,'Example Question?',5275),
(3425,141, 9,'Example Question?',5279),
(3426,141,10,'Example Question?',5285);
SELECT a.doctor_id
, a.test_id
, SUM(q.correct_answer_id IS NOT NULL)/COUNT(*) pct
FROM doctors_test_answers a
LEFT
JOIN test_questions q
ON q.id = a.test_question_id
AND q.correct_answer_id = a.test_answer_id
GROUP
BY doctor_id
, test_id;
----------- --------- --------
| doctor_id | test_id | pct |
----------- --------- --------
| 1661 | 141 | 0.9000 |
----------- --------- --------
Комментарии:
1. спасибо — вы переименовали таблицу и столбец, но логика есть — еще раз спасибо 🙂
2. Да, всегда нужно сохранять здравомыслие при решении этих проблем
Ответ №2:
Я могу предоставить следующее решение:
select
da.doctor_id,
d.initials,
da.test_id,
round(count(tq.test_answer_id)/count(test_question_id) * 100) result
from doctor_test_answers da
join doctors d on d.id = da.doctor_id
left join test_questions tq
on da.test_question_id = tq.id and da.test_answer_id = tq.test_answer_id
group by doctor_id, test_id, d.initials
;
Скрипка: SQLize.onine
Результат:
=========== ========== ========= ========
| doctor_id | initials | test_id | result |
=========== ========== ========= ========
| 1661 | AP | 141 | 90 |
----------- ---------- --------- --------