SQLite按category_id分组取每个分类价格最高最低各5条产品数据的实现

方案可行性说明 #

你提到的「正反排序查询后合并结果」的方案是可行的，针对SQLite环境有两种常用实现方式，优先推荐窗口函数方案：

方案1：窗口函数实现（SQLite 3.25及以上版本支持，推荐） #

利用ROW_NUMBER()窗口函数按分类分区排序，直接筛选每个分类的高低价各5条记录：

WITH ranked_products AS (
    SELECT 
        *,
        -- 每个分类内按价格升序排序，得到低价排名
        ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price ASC) AS rn_low,
        -- 每个分类内按价格降序排序，得到高价排名
        ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn_high
    FROM sql_data_users.products
)
SELECT price, category_id, product_id
FROM ranked_products
-- 筛选每个分类低价前5、高价前5的记录
WHERE rn_low <=5 OR rn_high <=5
-- 可选：按分类、价格排序方便查看
ORDER BY category_id, price ASC;

补充说明： #

• 如果需要保留同价格的所有并列记录，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()
• 如果分类总记录数不足10条，会自动返回该分类全部记录，不会重复返回

方案2：双查询合并实现（兼容低版本SQLite） #

就是你提到的正反排序后合并的方案，实现逻辑如下：

-- 取每个分类价格最低的5条
SELECT * FROM sql_data_users.products p1
WHERE 5 > (
    SELECT COUNT(*) FROM sql_data_users.products p2
    WHERE p2.category_id = p1.category_id AND p2.price < p1.price
)
UNION
-- 取每个分类价格最高的5条
SELECT * FROM sql_data_users.products p1
WHERE 5 > (
    SELECT COUNT(*) FROM sql_data_users.products p2
    WHERE p2.category_id = p1.category_id AND p2.price > p1.price
)
ORDER BY category_id, price ASC;

补充说明： #

• 用UNION会自动去重，避免分类记录不足10条时重复返回相同记录，不需要去重可替换为UNION ALL提升效率
• 子查询的逻辑是统计当前分类下比当前记录价格低/高的记录数，小于5就符合前5的条件

原查询错误原因 #

你原来的写法有两个核心问题：

• GROUP BY product_id,category_id在这里仅起到去重作用，并不会实现「按分类取每组N条」的效果
• 全局LIMIT 10是限制整个查询结果的总条数，不会按分类分别限制条数

内容的提问来源于stack exchange，提问作者Cena sha'bani