如何使SQL查询每个产品类别固定返回5行（含空值占位行）

当然可以搞定这个需求！我经常遇到类似的报表场景——需要固定行数来对齐展示，不管实际数据有多少。核心思路其实很简单：先给每个产品类别预生成5个「占位位置」（编号1到5），再把真实的Top5产品数据和这些位置做左连接，这样不足5个产品的类别，剩下的位置就会自动用NULL填充啦。下面我给你分几种主流数据库的具体实现代码：

1. SQL Server / Azure SQL #

用交叉连接生成占位位置，再和TopN数据关联：

WITH CategoryRanks AS (
    -- 先获取每个类别下按销售额降序的Top5产品及排名
    SELECT 
        p.CategoryID,
        p.ProductID,
        p.ProductName,
        s.SalesAmount,
        ROW_NUMBER() OVER (PARTITION BY p.CategoryID ORDER BY s.SalesAmount DESC) AS RankNum
    FROM Products p
    JOIN Sales s ON p.ProductID = s.ProductID
),
CategoryPositions AS (
    -- 为每个类别生成1-5的占位行号
    SELECT DISTINCT 
        CategoryID,
        n.Num AS Position
    FROM Products
    CROSS JOIN (VALUES (1),(2),(3),(4),(5)) n(Num)
)
SELECT 
    cp.CategoryID,
    cr.ProductID,
    cr.ProductName,
    cr.SalesAmount
FROM CategoryPositions cp
LEFT JOIN CategoryRanks cr 
    ON cp.CategoryID = cr.CategoryID 
    AND cp.Position = cr.RankNum
ORDER BY cp.CategoryID, cp.Position;

2. PostgreSQL #

PostgreSQL自带的generate_series函数可以更简洁地生成连续数字：

WITH CategoryRanks AS (
    SELECT 
        p.category_id,
        p.product_id,
        p.product_name,
        s.sales_amount,
        ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY s.sales_amount DESC) AS rank_num
    FROM products p
    JOIN sales s ON p.product_id = s.product_id
),
CategoryPositions AS (
    SELECT 
        c.category_id,
        generate_series(1,5) AS position
    FROM (SELECT DISTINCT category_id FROM products) c
)
SELECT 
    cp.category_id,
    cr.product_id,
    cr.product_name,
    cr.sales_amount
FROM CategoryPositions cp
LEFT JOIN CategoryRanks cr 
    ON cp.category_id = cr.category_id 
    AND cp.position = cr.rank_num
ORDER BY cp.category_id, cp.position;

3. MySQL 8.0+ #

MySQL 8.0及以上支持窗口函数和CTE，用UNION生成1-5的数字序列：

WITH CategoryRanks AS (
    SELECT 
        p.CategoryID,
        p.ProductID,
        p.ProductName,
        s.SalesAmount,
        ROW_NUMBER() OVER (PARTITION BY p.CategoryID ORDER BY s.SalesAmount DESC) AS RankNum
    FROM Products p
    JOIN Sales s ON p.ProductID = s.ProductID
),
Numbers AS (
    SELECT 1 AS Num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
),
CategoryPositions AS (
    SELECT DISTINCT 
        p.CategoryID,
        n.Num AS Position
    FROM Products p
    CROSS JOIN Numbers n
)
SELECT 
    cp.CategoryID,
    cr.ProductID,
    cr.ProductName,
    cr.SalesAmount
FROM CategoryPositions cp
LEFT JOIN CategoryRanks cr 
    ON cp.CategoryID = cr.CategoryID 
    AND cp.Position = cr.RankNum
ORDER BY cp.CategoryID, cp.Position;

关键逻辑说明 #

1. CategoryRanks：通过ROW_NUMBER()窗口函数，给每个类别下的产品按销售额降序排名，后续只匹配1-5的位置，自然过滤掉排名靠后的产品。
2. CategoryPositions：为每个产品类别生成5个占位行（编号1到5），确保每个类别都有5个“空位”。
3. 左连接关联：将占位位置和真实TopN数据关联，没有匹配到真实产品的位置会自动填充NULL，最终每个类别固定返回5行。

额外注意点 #

如果你的业务需要包含没有任何产品的空类别，只需要把生成CategoryPositions时的数据源从Products表改成Categories表即可，这样即使类别下没有产品，也会生成5个NULL行。

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