如何重写一个语句，它使用MySQL扩展组BY到一个标准的SQL语句

Question

使用MySQL Extensions to GROUP BY，当sql_mode设置为ONLY_FULL_GROUP_BY时，不能执行。

-- SET sql_mode = ""; -- reset sql_mode and execute statement
SELECT
    `courses`.`id` AS `id`,
    `courses`.`title` AS `title`,
    `courses`.`description` AS `description`,
    MATCH (coursedata.title) AGAINST ('Salsa') * 5 + MATCH (coursedata.description) AGAINST ('Salsa') * 2 AS `relevance`
FROM `courses`
INNER JOIN `coursedata` ON `courses`.`id` = `coursedata`.`id`
GROUP BY `courses`.`id`
HAVING `relevance` >= '3'

是否有可能达到与标准SQL相同的效果?

Answer 1

似乎工作…

SELECT * FROM (
    SELECT
        DISTINCT `courses`.`id` AS `id`,
        `courses`.`title` AS `title`,
        `courses`.`description` AS `description`,
        MATCH (coursedata.title) AGAINST ('Salsa') * 5 + MATCH (coursedata.description) AGAINST ('Salsa') * 2 AS `relevance`
    FROM `courses`
    INNER JOIN `coursedata` ON `courses`.`id` = `coursedata`.`id`
) AS subselect
WHERE `relevance` >= '3'