mysql - 简化多个 SELECT 语句的 SQL 查询，每个语句都带有 UNION 和 LIMIT

Question

需要帮助将此 SQL 查询简化为单个 SELECT:

(SELECT * FROM `deals`
WHERE category_id = 1
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 2
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 4
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 5
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 6
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 8
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 9
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 10
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 17
ORDER BY id desc
LIMIT 10)

有人告诉我尝试使用 GROUP BY 和 HAVING。然而，我尝试过的任何查询都没有丝毫效果...

任何帮助将不胜感激!

编辑 - 抱歉，忘记提及数据库引擎是 MySQL

Answer 1

您可以使用窗口函数将其压缩，将每个组存储桶限制为 10。

SELECT
    *
FROM
(
    SELECT 
        *,
        ROW_NUMBER() OVER PARTITION BY(category_id ORDER BY id DESC) AS GroupOrder
    FROM `deals`
    WHERE category_id BETWEEN 1 AND 10
)AS X
WHERE
    GroupOrder<=10