MySQL 删除行 ORDER BY COUNT DESC

Question

我有一个表，其中包含测试库每两个问题的相似度。

这意味着 Question_id 6 与 Question_id 10 相似度为 84%。Question_id 6 的类似问题共有 12 个。

我只是最相关的问题，或者前 7 个相关问题。

我见过Mysql delete order by并尝试过:

DELETE FROM     exam_relatedquestion
WHERE
    `exam_relatedquestion`.id IN (
        SELECT
            `exam_relatedquestion`.id
        FROM
            (
                SELECT  `exam_relatedquestion`.id
                FROM    `exam_relatedquestion`
                GROUP BY
                    `exam_relatedquestion`.from_question_id_id
                ORDER BY
                    `exam_relatedquestion`.similarity DESC
                LIMIT 7
            ) a
    )

但是错误信息是:

[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'den.exam_relatedquestion.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

如何删除与问题不相关的前 7 行？

Answer 1

那是行不通的。无论如何，您的伪代码都不正确，因为排序方向错误。

无论如何，您都可以使用变量来枚举问题，然后使用join:

delete erq
    from exam_relatedquestion erq join
         (select erq2.*,
                 (@rn := if(@q = erq2.from_question_id_id, @rn + 1,
                            if(@q := erq2.from_question_id_id, 1, 1)
                           )
                 ) as seqnum
          from exam_relatedquestion erq2 cross join
               (select @rn := 0, @q := -1) params
          order by erq2.from_question_id_id, score desc
         ) erq2
         on erq2.id = erq.id
    where rn > 7;