SQLite查询：获取每个category_id下价格最高最低各10条记录

你提到的用UNION组合分组查询的思路完全可行，这里给出两种常见实现方式：

方案1：窗口函数实现（SQLite 3.25+ 推荐） #

SELECT product_id, category_id, price
FROM (
    -- 取每个分类价格最高的10条
    SELECT 
        product_id, 
        category_id, 
        price,
        ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
    FROM product
) t1
WHERE rn <= 10

UNION ALL

SELECT product_id, category_id, price
FROM (
    -- 取每个分类价格最低的10条
    SELECT 
        product_id, 
        category_id, 
        price,
        ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price ASC) AS rn
    FROM product
) t2
WHERE rn <= 10
-- 可选：统一排序规则，按分类+价格排序方便查看
ORDER BY category_id, price DESC;

逻辑说明：

• 用PARTITION BY category_id按分类分组，ROW_NUMBER()给每组内的商品按价格排序生成序号
• 倒序排序取前10即为最高价10条，正序排序取前10即为最低价10条
• 题目明确无重复价格，因此两部分结果没有重叠，用UNION ALL比UNION性能更高

方案2：关联子查询实现（兼容旧版SQLite） #

如果你的SQLite版本不支持窗口函数，可以用关联子查询实现：

-- 取每个分类价格最高的10条
SELECT p1.product_id, p1.category_id, p1.price
FROM product p1
WHERE (
    SELECT COUNT(*) 
    FROM product p2 
    WHERE p2.category_id = p1.category_id AND p2.price > p1.price
) < 10

UNION ALL

-- 取每个分类价格最低的10条
SELECT p1.product_id, p1.category_id, p1.price
FROM product p1
WHERE (
    SELECT COUNT(*) 
    FROM product p2 
    WHERE p2.category_id = p1.category_id AND p2.price < p1.price
) < 10
ORDER BY category_id, price DESC;

内容的提问来源于stack exchange，提问作者Tanhaeirad