31 lines
836 B
SQL
31 lines
836 B
SQL
DROP TABLE IF EXISTS tmptable;
|
|
|
|
CREATE temporary TABLE tmptable AS
|
|
SELECT DISTINCT( s.language ),
|
|
(SELECT macroscoreid
|
|
FROM score AS s1
|
|
WHERE s1.language = s.language
|
|
ORDER BY faliurerate ASC
|
|
LIMIT 1) AS macroscoreidbest
|
|
FROM score AS s;
|
|
|
|
SELECT tmptable.language,
|
|
macroscoreidbest,
|
|
correct,
|
|
incorrect
|
|
FROM tmptable
|
|
JOIN accuracy
|
|
ON accuracy.macroscoreid = macroscoreidbest
|
|
AND accuracy.language = tmptable.language;
|
|
|
|
SELECT tmptable.language,
|
|
macroscoreidbest,
|
|
strkey,
|
|
intval,
|
|
type
|
|
FROM tmptable
|
|
JOIN counttable
|
|
ON counttable.macroscoreid = macroscoreidbest
|
|
AND counttable.language = tmptable.language;
|
|
|
|
DROP TABLE tmptable;
|