如何通过SQL Select语句始终返回10行数据（不足时补空白行）

核心思路是先构造一个包含10行的数字序列，再将这个序列与你的业务查询结果做左关联——不管原查询返回多少行，最终结果都会保留10行，原数据不足的部分会以NULL（空白行）填充。

以下是主流数据库的具体实现：

MySQL #

方法1（MySQL 8.0+ 支持CTE） #

WITH numbers AS (
    -- 生成1-10的数字序列
    SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
),
original_query AS (
    -- 替换为你的实际查询，添加行号用于关联
    SELECT 
        col1, col2, col3, -- 你的业务字段
        ROW_NUMBER() OVER (ORDER BY col1) AS row_num -- 按需要的字段排序，保证行号唯一
    FROM your_table
)
SELECT oq.col1, oq.col2, oq.col3
FROM numbers n
LEFT JOIN original_query oq ON n.num = oq.row_num;

方法2（兼容MySQL 5.x） #

如果你的MySQL版本不支持CTE，用子查询替代：

SELECT oq.col1, oq.col2, oq.col3
FROM (
    SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) AS numbers
LEFT JOIN (
    SELECT 
        col1, col2, col3,
        @row_num := @row_num + 1 AS row_num
    FROM your_table, (SELECT @row_num := 0) AS init
    ORDER BY col1
) AS oq ON numbers.num = oq.row_num;

SQL Server #

WITH numbers AS (
    -- 生成1-10的数字序列，用sys.all_columns快速生成
    SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
    FROM sys.all_columns
),
original_query AS (
    SELECT 
        col1, col2, col3,
        ROW_NUMBER() OVER (ORDER BY col1) AS row_num
    FROM your_table
)
SELECT oq.col1, oq.col2, oq.col3
FROM numbers n
LEFT JOIN original_query oq ON n.num = oq.row_num;

PostgreSQL #

PostgreSQL有原生的序列生成函数，写法更简洁：

WITH numbers AS (
    SELECT generate_series(1, 10) AS num
),
original_query AS (
    SELECT 
        col1, col2, col3,
        ROW_NUMBER() OVER (ORDER BY col1) AS row_num
    FROM your_table
)
SELECT oq.col1, oq.col2, oq.col3
FROM numbers n
LEFT JOIN original_query oq ON n.num = oq.row_num;

关键注意点 #

• 排序逻辑：ROW_NUMBER()里的ORDER BY要指定你业务需要的排序字段，确保原数据的行顺序符合预期
• 空白行自定义：关联后无匹配的行会显示NULL，如果需要将NULL替换为特定空白值（比如空字符串），可以用COALESCE函数，例如COALESCE(oq.col1, '') AS col1
• 性能：生成10行序列的开销可以忽略，不会影响查询性能

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