时间戳

首页 > 解决方案 > SQL - 显示第一个表中的所有值

sql - SQL - 显示第一个表中的所有值

问题描述

我想使用此查询在表格“国家”中显示“国家.nationen_bez”列的所有值：

SELECT DISTINCT nation.nationen_bez,   
         NVL(SUM (debitor.flimit_deb) OVER (PARTITION BY nation.nationen_bez ORDER BY nation.nationen_bez),0) AS korisceno,   
         NVL(nation_ext.country_limit,0) - NVL(SUM (debitor.flimit_deb) OVER (PARTITION BY nation.nationen_bez ORDER BY nation.nationen_bez),0) AS se_na_voljo,   
         NVL(nation_ext.country_limit,0) AS odobren_limit  
    FROM debitor,   
         nation,   
         nation_ext,   
         firmenstamm,   
         debitorenstamm  
   WHERE ( nation.nationen_kode = debitorenstamm.nationen_kode (+)) and  
         ( nation.nationen_id = nation_ext.nationen_id (+)) and  
         ( debitor.debitoren_id = debitorenstamm.debitoren_id ) and  
         ( debitorenstamm.waehrungs_id = firmenstamm.waehrungs_id ) and  
         ( ( debitor.risiko = 1 ) AND  
         ( debitor.factoringart = 'EF' ))

通过这个查询，我只得到那些存在 debitor.risiko = 1 和 debitor.factoringart = 'EF' 的借方的“nation.nationen_bez”。'NVL(SUM (debitor.flimit_deb) OVER (PARTITION BYnation.nationen_bez ORDER BYnation.nationen_bez),0) AS korisceno' 需要这个条件，因为对于那些有 debitor.risiko 的债务人，我只希望 sum 'flimit_deb' = 1 和 debitor.factoringart = 'EF' - 我怎样才能改变这个状态？

tnx 提前

标签： sql

解决方案

我用过左连接，从国家开始

SELECT DISTINCT nation.nationen_bez, 
SUM (debitor.flimit_deb) OVER (PARTITION BY nation.nationen_bez ORDER BY nation.nationen_bez) AS korisceno,   
nation_ext.country_limit - SUM (debitor.flimit_deb) OVER (PARTITION BY nation.nationen_bez ORDER BY nation.nationen_bez) AS se_na_voljo,   
nation_ext.country_limit AS odobren_limit  
FROM nation
left join debitorenstamm on nation.nationen_kode = debitorenstamm.nationen_kode
left join debitor on debitorenstamm.debitoren_id =  debitor.debitoren_id and debitor.risiko = 1 AND  debitor.factoringart = 'EF' 
left join nation_ext on nation.nationen_id = nation_ext.nationen_id   
left join firmenstamm on  debitorenstamm.waehrungs_id = firmenstamm.waehrungs_id

推荐阅读