这样多条MYSQL语句能不能一句SQL搞定？-灵析社区

方法1： mysql >= 8.0 WITH ranked_data AS ( SELECT *, COUNT(*) OVER (PARTITION BY title, mark) AS count, ROW_NUMBER() OVER (PARTITION BY mark ORDER BY COUNT(*) DESC) AS row_num FROM t_search WHERE mark BETWEEN 'a' AND 'z' GROUP BY title, mark ) SELECT * FROM ranked_data WHERE row_num <= 20 ORDER BY mark, count DESC; 方法2： mysql < 8.0的 SELECT * FROM ( SELECT *, @rank := IF(@prev_mark = mark, @rank + 1, 1) AS rank, @prev_mark := mark, COUNT(*) AS count FROM t_search JOIN (SELECT @rank := 0, @prev_mark := '') AS vars WHERE mark BETWEEN 'a' AND 'z' GROUP BY title, mark ORDER BY mark, count DESC ) AS ranked_data WHERE rank <= 20 ORDER BY mark, count DESC;