Firebird 2.5中关联两表，按条件返回求和结果或空值

表结构 #

BONPOS表（简称P） #

CREATE TABLE BONPOS (
    BON_ID Integer NOT NULL,
    POS_ZEILE Integer NOT NULL,
    Z_KASSE_ID Varchar(30),
    GV_TYP Integer NOT NULL,
    KASSENABSCHLUSS_NR Integer
);
ALTER TABLE BONPOS ADD PRIMARY KEY (BON_ID,POS_ZEILE);

KASSE_BONPOS_UST表（简称PU） #

CREATE TABLE KASSE_BONPOS_UST (
    BON_ID Integer NOT NULL,
    POS_ZEILE Integer NOT NULL,
    Z_KASSE_ID Varchar(30),
    KASSENABSCHLUSS_NR Integer,
    POS_BRUTTO Numeric(15,2)
);

查询需求 #

• 当P.GV_TYP = 14时，返回对应KASSENABSCHLUSS_NR下PU.POS_BRUTTO的求和值
• 当P.GV_TYP ≠ 14时，返回NULL或0
• 需保留所有KASSENABSCHLUSS_NR的记录，即使求和值为NULL

现有查询问题 #

1. 初始内连接查询仅返回GV_TYP=14且有对应POS_BRUTTO数据的分组，丢失无数据的KASSENABSCHLUSS_NR：

select pu.KASSENABSCHLUSS_NR, SUM(pu.POS_BRUTTO)
from BONPOS_UST pu, BONPOS p
where (pu.Z_KASSE_ID = 'MeineKasse') and
      (pu.KASSENABSCHLUSS_NR = p.KASSENABSCHLUSS_NR) and
      (pu.BON_ID = p.BON_ID) and
      (p.GV_TYP = 14)
group by pu.KASSENABSCHLUSS_NR
order by pu.KASSENABSCHLUSS_NR

2. 后续左连接查询因表名不匹配，仅返回求和值为NULL的记录，丢失有效求和数据：

select p.KASSENABSCHLUSS_NR, SUM(pu.POS_BRUTTO)
from KASSE_BONPOS p
left join KASSE_BONPOS_UST pu
on (pu.Z_KASSE_ID = 'MeineKasse') and
   (pu.KASSENABSCHLUSS_NR = p.KASSENABSCHLUSS_NR) and
   (pu.BON_ID = p.BON_ID)
WHERE (p.GV_TYP = 14)
group by p.KASSENABSCHLUSS_NR
order by p.KASSENABSCHLUSS_NR 

期望输出 #

解决方案 #

方案1：保留所有KASSENABSCHLUSS_NR，按GV_TYP返回对应值 #

先提取所有唯一的KASSENABSCHLUSS_NR作为基础数据集，再关联两张表计算求和值：

select 
    all_kn.KASSENABSCHLUSS_NR,
    case 
        when p.GV_TYP = 14 then sum(pu.POS_BRUTTO)
        else null -- 需返回0则替换为0
    end as SUM
from (
    -- 获取所有存在的KASSENABSCHLUSS_NR
    select distinct KASSENABSCHLUSS_NR 
    from BONPOS
) all_kn
left join BONPOS p 
    on all_kn.KASSENABSCHLUSS_NR = p.KASSENABSCHLUSS_NR
left join KASSE_BONPOS_UST pu 
    on p.KASSENABSCHLUSS_NR = pu.KASSENABSCHLUSS_NR 
    and p.BON_ID = pu.BON_ID 
    and pu.Z_KASSE_ID = 'MeineKasse'
group by all_kn.KASSENABSCHLUSS_NR, p.GV_TYP
order by all_kn.KASSENABSCHLUSS_NR

方案2：仅保留GV_TYP=14的分组，包含求和值为NULL的记录 #

统一表名后调整左连接逻辑，确保保留所有GV_TYP=14的KASSENABSCHLUSS_NR：

select 
    p.KASSENABSCHLUSS_NR,
    sum(pu.POS_BRUTTO) as SUM
from BONPOS p
left join KASSE_BONPOS_UST pu 
    on p.KASSENABSCHLUSS_NR = pu.KASSENABSCHLUSS_NR 
    and p.BON_ID = pu.BON_ID 
    and pu.Z_KASSE_ID = 'MeineKasse'
where p.GV_TYP = 14
group by p.KASSENABSCHLUSS_NR
order by p.KASSENABSCHLUSS_NR

关键说明 #

• 方案1通过子查询确保所有KASSENABSCHLUSS_NR都被纳入结果
• 方案2需注意表名一致性，避免因表名错误导致数据丢失
• case语句可灵活切换返回NULL或0，满足不同需求

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