Oracle查询需返回固定行数：无数据时仍保留预设分组桶

我之前也碰到过完全一样的需求——不管对应区间有没有数据，必须保留预设的那7个分组桶，空桶还要显示0计数。这里给你两个实用方案，按需选就行：

方案一：纯Oracle SQL实现（推荐，数据库层面处理更高效） #

直接在SQL里生成固定的分组桶列表，再和业务查询结果做左连接，就能保证所有桶都被保留，空桶的计数自动补0，不用在代码里额外处理。

示例代码：

WITH predefined_buckets AS (
    SELECT 'Within 5 days' AS bucket_name FROM DUAL UNION ALL
    SELECT '6-10 days' FROM DUAL UNION ALL
    SELECT '11-15 days' FROM DUAL UNION ALL
    SELECT '16-20 days' FROM DUAL -- 注意原需求里的"15-20 days"可能是笔误，改成16-20避免区间重叠，可按需调整
    SELECT '21-25 days' FROM DUAL UNION ALL
    SELECT '26-30 days' FROM DUAL UNION ALL
    SELECT '> 30 days' FROM DUAL
),
data_counts AS (
    -- 替换成你的实际业务查询，计算每个桶的记录数
    SELECT 
        CASE
            WHEN days_diff <=5 THEN 'Within 5 days'
            WHEN days_diff BETWEEN 6 AND 10 THEN '6-10 days'
            WHEN days_diff BETWEEN 11 AND 15 THEN '11-15 days'
            WHEN days_diff BETWEEN 16 AND 20 THEN '16-20 days'
            WHEN days_diff BETWEEN 21 AND 25 THEN '21-25 days'
            WHEN days_diff BETWEEN 26 AND 30 THEN '26-30 days'
            WHEN days_diff >30 THEN '> 30 days'
        END AS bucket_name,
        COUNT(*) AS record_count
    FROM your_table
    -- 这里添加你的WHERE过滤条件
    GROUP BY 
        CASE
            WHEN days_diff <=5 THEN 'Within 5 days'
            WHEN days_diff BETWEEN 6 AND 10 THEN '6-10 days'
            WHEN days_diff BETWEEN 11 AND 15 THEN '11-15 days'
            WHEN days_diff BETWEEN 16 AND 20 THEN '16-20 days'
            WHEN days_diff BETWEEN 21 AND 25 THEN '21-25 days'
            WHEN days_diff BETWEEN 26 AND 30 THEN '26-30 days'
            WHEN days_diff >30 THEN '> 30 days'
        END
)
SELECT 
    pb.bucket_name,
    NVL(dc.record_count, 0) AS record_count
FROM predefined_buckets pb
LEFT JOIN data_counts dc ON pb.bucket_name = dc.bucket_name
ORDER BY 
    -- 按预设顺序排序，避免结果乱序
    CASE pb.bucket_name
        WHEN 'Within 5 days' THEN 1
        WHEN '6-10 days' THEN 2
        WHEN '11-15 days' THEN 3
        WHEN '16-20 days' THEN 4
        WHEN '21-25 days' THEN 5
        WHEN '26-30 days' THEN 6
        WHEN '> 30 days' THEN 7
    END;

小提示：如果days_diff是计算出来的（比如当前日期和表中字段的差值），直接把计算逻辑替换到CASE语句里就行；区间重叠的问题一定要注意，不然会导致数据统计错误。

方案二：优化后的LINQ实现 #

如果还是想在代码层面用LINQ处理，可以简化空桶的生成逻辑，不用手动判断每个桶是否存在，直接用预设桶数组和查询结果做左外连接：

假设你的业务查询返回IEnumerable<BucketCount>，其中BucketCount包含BucketName和Count两个属性：

// 预设的固定桶列表
var predefinedBuckets = new[] {
    "Within 5 days", "6-10 days", "11-15 days", "16-20 days", 
    "21-25 days", "26-30 days", "> 30 days"
};

// 你的业务查询，获取有数据的桶计数
var dataBuckets = yourDbContext.YourTable
    .Where(/* 你的过滤条件 */)
    .GroupBy(x => {
        var daysDiff = (DateTime.Now - x.YourDateField).Days;
        return daysDiff switch {
            <=5 => "Within 5 days",
            >=6 and <=10 => "6-10 days",
            >=11 and <=15 => "11-15 days",
            >=16 and <=20 => "16-20 days",
            >=21 and <=25 => "21-25 days",
            >=26 and <=30 => "26-30 days",
            _ => "> 30 days"
        };
    })
    .Select(g => new BucketCount { BucketName = g.Key, Count = g.Count() });

// 左连接预设桶和数据桶，空桶自动补0
var result = from bucket in predefinedBuckets
             join data in dataBuckets on bucket equals data.BucketName into joined
             from j in joined.DefaultIfEmpty()
             select new BucketCount {
                 BucketName = bucket,
                 Count = j?.Count ?? 0
             };

这种方式比手动遍历桶列表判断是否存在要简洁得多，逻辑也更清晰。

两种方案各有优劣：纯SQL方案适合数据量大的场景，能减少数据库和应用之间的数据传输；LINQ方案适合需要在代码里灵活调整桶逻辑的场景，你可以根据实际情况选择~

内容的提问来源于stack exchange，提问作者J.Romeo