这样多条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.6k
2 个回答

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

SELECT

            
            sum(case when w.`mark` = 'a' then 1 else 0 end ) count1,
            sum(case when w.`status` = 'b' then 1 else 0 end ) count2,
            t_search w;

这样行不?

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题
宣传栏