这样多条MYSQL语句能不能一句SQL搞定？-灵析社区
谁能阻止我删代码SELECT *, COUNT(*) AS count FROM t_search WHERE mark = 'a' GROUP BY title ORDER BY count DESC LIMIT 0, 20 SELECT *, COUNT(*) AS count FROM t_search WHERE mark = 'b' GROUP BY title ORDER BY count DESC LIMIT 0, 20 SELECT *, COUNT(*) AS count FROM t_search WHERE mark = 'c' GROUP BY title ORDER BY count DESC LIMIT 0, 20 ... 一共24条SQL，mark 是 a-z 每句SQL查询20条
米小米会努力方法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;