基于prty条件返回指定FCST值的SQL实现问询

1. 能否在HAVING语句中使用IIf或CASE函数？ #

可以，多数SQL方言（包括你使用的Access SQL）支持在HAVING子句中使用IIf或CASE函数。不过在你的场景下，用HAVING实现"优先返回prty='D'记录"的需求并不合适——HAVING是对分组后的结果集进行过滤，而你需要的是从多条分组结果中选择优先级最高的一条，而非过滤掉某些分组。

2. 实现需求的解决方案 #

你的核心需求是：针对零件号00104918和仓库77，若存在prty='D'的记录则返回该条，否则返回prty='01'的记录。以下是两种可行方案：

方案1：排序+TOP 1（适配Access SQL） #

通过排序让prty='D'的记录排在最前面，再只取第一条结果：

SELECT TOP 1 
    dbo_forecast.mtl_part_part_nbr,
    SUM(Round(dbo_forecast.forecast_qty)) AS FCST,
    dbo_m8stacd_standards.whse_nbr,
    dbo_m8stacd_standards.statn_cd,
    dbo_m8stacd_standards.prty
INTO tblforecast77
FROM dbo_m8stacd_standards
INNER JOIN dbo_forecast 
    ON dbo_m8stacd_standards.part_nbr = dbo_forecast.mtl_part_part_nbr
WHERE 
    dbo_forecast.type IN (11, 12)
    AND dbo_forecast.forecast_month IN ('01','02','03','04','05','06','07','08','09','10','11','12')
    AND dbo_forecast.mtl_part_part_nbr = '00104918'
    AND dbo_m8stacd_standards.whse_nbr = 77
    AND dbo_m8stacd_standards.prty IN ('01', 'D')
GROUP BY 
    dbo_forecast.mtl_part_part_nbr,
    dbo_m8stacd_standards.whse_nbr,
    dbo_m8stacd_standards.statn_cd,
    dbo_m8stacd_standards.prty
HAVING SUM(Round(dbo_forecast.forecast_qty)) > 0
ORDER BY 
    CASE WHEN dbo_m8stacd_standards.prty = 'D' THEN 1 ELSE 2 END; -- 让'D'优先级更高

方案2：窗口函数（适配SQL Server/PostgreSQL等） #

如果你的数据库支持窗口函数，可以给每条记录标记优先级排名，再筛选排名第一的记录：

WITH RankedForecasts AS (
    SELECT 
        dbo_forecast.mtl_part_part_nbr,
        SUM(Round(dbo_forecast.forecast_qty)) AS FCST,
        dbo_m8stacd_standards.whse_nbr,
        dbo_m8stacd_standards.statn_cd,
        dbo_m8stacd_standards.prty,
        ROW_NUMBER() OVER (
            PARTITION BY dbo_forecast.mtl_part_part_nbr, dbo_m8stacd_standards.whse_nbr
            ORDER BY CASE WHEN dbo_m8stacd_standards.prty = 'D' THEN 1 ELSE 2 END
        ) AS rn
    FROM dbo_m8stacd_standards
    INNER JOIN dbo_forecast 
        ON dbo_m8stacd_standards.part_nbr = dbo_forecast.mtl_part_part_nbr
    WHERE 
        dbo_forecast.type IN (11, 12)
        AND dbo_forecast.forecast_month IN ('01','02','03','04','05','06','07','08','09','10','11','12')
        AND dbo_forecast.mtl_part_part_nbr = '00104918'
        AND dbo_m8stacd_standards.whse_nbr = 77
        AND dbo_m8stacd_standards.prty IN ('01', 'D')
    GROUP BY 
        dbo_forecast.mtl_part_part_nbr,
        dbo_m8stacd_standards.whse_nbr,
        dbo_m8stacd_standards.statn_cd,
        dbo_m8stacd_standards.prty
    HAVING SUM(Round(dbo_forecast.forecast_qty)) > 0
)
SELECT 
    mtl_part_part_nbr,
    FCST,
    whse_nbr,
    statn_cd,
    prty
INTO tblforecast77
FROM RankedForecasts
WHERE rn = 1;

额外优化说明 #

• 用IN替代多个OR条件，简化语句结构；
• 将零件号、仓库号这类过滤条件从HAVING移到WHERE，提前缩小数据集，提升查询效率。

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