23 lines
785 B
MySQL
23 lines
785 B
MySQL
|
CREATE OR REPLACE DEFINER=`root`@`localhost`
|
||
|
|
SQL SECURITY DEFINER
|
||
|
|
VIEW `psico`.`results`
|
||
|
|
AS SELECT `eq`.`examFk` AS `examFk`,
|
||
|
|
sum(`a`.`correct` * `a`.`correct`) AS `aciertos`,
|
||
|
|
`e`.`questionsCount` AS `questionsCount`,
|
||
|
|
HOUR(timediff(`e`.`finished`, `e`.`started`)) * 60 + MINUTE(timediff(`e`.`finished`, `e`.`started`)) AS `minutos`,
|
||
|
|
least(
|
||
|
|
3,
|
||
|
|
3 * (`e`.`questionsCount` * 0.5) / (
|
||
|
|
HOUR(timediff(`e`.`finished`, `e`.`started`)) * 60 + MINUTE(timediff(`e`.`finished`, `e`.`started`))
|
||
|
|
)
|
||
|
|
) + 7 * sum(`a`.`correct` * `a`.`correct`) / `e`.`questionsCount` AS `nota`
|
||
|
|
FROM (
|
||
|
|
(
|
||
|
|
`psico`.`exam` `e`
|
||
|
|
JOIN `psico`.`examQuestion` `eq` ON(`eq`.`examFk` = `e`.`id`)
|
||
|
|
)
|
||
|
|
JOIN `psico`.`answer` `a` ON(`eq`.`answerFk` = `a`.`id`)
|
||
|
|
)
|
||
|
|
GROUP BY `eq`.`examFk`,
|
||
|
|
`e`.`questionsCount`
|