简化多个select语句的sql查询，每个语句都有union和limit

szqfcxe2 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(418)

需要帮助将此sql查询简化为单选：

(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)

有人告诉我，要试着用“分组”和“有”。然而，我尝试的任何查询都没有任何效果。。。
任何帮助都将不胜感激！
编辑-抱歉，忘记提到数据库引擎是mysql

sql mysql union sql-limit

来源：https://stackoverflow.com/questions/51615946/simplify-sql-query-of-multiple-select-statements-each-with-union-and-limit

3条答案

按热度按时间

4xrmg8kj1#

对于没有窗口功能的老版本mysql，下面是代码。

SELECT T1.ID, T1.Category_ID, T1.Name
FROM (
SELECT  @row_num := IF(@prev_value=concat_ws('',t.Category_ID),@row_num+1,1) AS RowNumber
         ,t.*
         ,@prev_value := concat_ws('',t.Category_ID)
    FROM data t,
         (SELECT @row_num := 1) x,
         (SELECT @prev_value := '') y
   ORDER BY t.Category_ID
   ) T1
  WHERE T1.RowNumber < 10
  AND T1.Category_ID IN (1,2,3,4,5,6,7,8,9,10)

您需要将必要的字段名添加到另一个select。
这使用了这里描述的技术

赞(0）回复(0）举报 2021-06-20

bz4sfanl2#

我不确定，我需要知道你是否需要10的限制，这是不是像拿所有这些东西的前10？
如果没有，那么

SELECT * FROM `deals`
WHERE category_id between 0 and 10 or category_id=17
ORDER BY category_id asc,  id desc

赞(0）回复(0）举报 2021-06-20

o75abkj43#

您可以使用一个窗口函数将其压缩，将每个组bucket限制为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

赞(0）回复(0）举报 2021-06-20

我来回答

简化多个select语句的sql查询，每个语句都有union和limit

3条答案

相关问题

热门标签

最新问答