mysql - 这样多条MYSQL语句能不能一句SQL搞定？

Question

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条

Answer 1

方法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;