sql - 向查询结果中添加 "empty"行

Question

我真的被这个任务困住了——我需要根据特定条件用“0”值的行填充报告，下面是示例:

假设我们有一张 BROKERS 表，其中包含公司名称、交易类型以及当月和以往所有月份的累计金额。

COMPANY       TRAN_TYPE          CURR_MNTH         ALL_MNTH  
Broker1       CURRENCY_SELL      $1000.00          $1500000.00 
Broker1       GOLD_SELL          $50000.00         $2500000.00 
Broker1       GOLD_BUY           $80000.00         $8500000.00 
Broker1       STOCKS_SELL        $35000.00         $3500000.00 

表 BROKERS 没有字段 TRAN_TYPE，但有一个字段 TRAN_TYPE_CD 引用另一个名为 TRAN_TYPE_CD_EXPL 的表，其中解释了所有代码:

TRAN_TYPE_CD  TRAN_TYPE_CD_EXPLD
1             STOCKS_SELL
2             STOCKS_BUY
3             GOLD_SELL
4             GOLD_BUY
5             SILVER_SELL
6             SILVER_BUY
7             COPPER_SELL
8             COPPER_BUY
9             CURRENCY_SELL
10            CURRENCY_BUY

所以上面显示的结果只是这两个表的简单连接:

select  b.COMPANY, tt.TRAN_TYPE, b.CURR_MONTH, b.ALL_MNTH
from    BROKERS b, TRAN_TYPE_CD_EXPL tt
where   b.TRAN_TYPE_CD = tt.TRAN_TYPE_CD;

一切都很简单，但问题就是从这里开始的:我正在处理的报告应该如下所示:

COMPANY       MARKET      TRAN_TYPE          CURR_MNTH         ALL_MNTH  
Broker1       FOREX       CURRENCY_SELL      $1000.00          $1500000.00 
Broker1       FOREX       CURRENCY_BUY       $0.00             $5500000.00 
Broker1       CONTRACTS   GOLD_SELL          $50000.00         $2500000.00 
Broker1       CONTRACTS   GOLD_BUY           $80000.00         $8500000.00 
Broker1       STOCKMARKET STOCKS_SELL        $35000.00         $3500000.00 
Broker1       STOCKMARKET STOCKS_BUY         $0.00             $9500000.00 

所以让我解释一下:首先，报告应该包含一个 MARKET 列，该列应该根据 TRAN_TYPE 列中的值填充，但问题是数据库中没有表 MARKET，因此每次需要在报告或其他地方填充它时，你需要像这样使用解码(假设每个人都知道各种 tran_types 属于哪里):

SELECT DECODE (TRAN_TYPE_CD_EXPL.TRAN_TYPE_CD_EXPLD,
             'CURRENCY_SELL', 'FOREX',
             'CURRENCY_BUY', 'FOREX',
             'STOCKS_SELL', 'STOCKMARKET',
             'STOCKS_BUY', 'STOCKMARKET') AS MARKET,

或基于 TRAN_TYPE_CD 值:

SELECT DECODE (BROKERS.TRAN_TYPE_CD,
             9, 'FOREX',
             10, 'FOREX',
             1, 'STOCKMARKET',
             2, 'STOCKMARKET') AS MARKET,

2) 问题 2 更复杂:报告逻辑说 - 一家公司在特定组中至少有一笔交易(假设市场类型“FOREX”为 CURRENCY_SELL)，报告应填充来自其他 tran_types市场类型组为 0 美元，即使你这家公司在本月没有任何这些交易。所以在这种情况下，它应该填充行

Broker1       FOREX       CURRENCY_BUY       $0.00             $5500000.00

和

Broker1       STOCKMARKET STOCKS_BUY         $0.00             $9500000.00

问题是它将通过 unix 上的 plsql 批处理执行，因此它必须是单个查询。

非常感谢任何想法和/或建议!

谢谢

附言

它的 oracle 11gr2，具有只读角色。

Answer 1

首先，您是否有不能创建 MARKET 表的原因？这显然是最直接的解决方案。但是，如果您没有数据库访问权限并且必须将其破解到您的查询中，您可以这样做:

WITH m AS (
    select 'CURRENCY_SELL' as tran_type, 'FOREX' as market_name from dual
    UNION ALL
    select 'CURRENCY_BUY' as tran_type, 'FOREX' as market_name from dual
    UNION ALL
    select 'STOCKS_SELL' as tran_type, 'STOCKMARKET' as market_name from dual
    UNION ALL
    select 'STOCKS_BUY' as tran_type, 'STOCKMARKET' as market_name from dual
)
select
    b.company, m.tran_type, m.market_name, 
    nvl(v.curr_month, 0) as curr_month,
    nvl(v.all_mnth, 0) as all_mnth
from
    m
cross join
    (select distinct b.company from brokers) b
left join (
    select b.COMPANY, tt.TRAN_TYPE, b.CURR_MONTH, b.ALL_MNTH
    from    BROKERS b, TRAN_TYPE_CD_EXPL tt
    where   b.TRAN_TYPE_CD = tt.TRAN_TYPE_CD
) v
on v.company = b.company and v.tran_type = m.tran_type;

首先，我使用内联市场 View 对公司进行了交叉连接 - 您将获得每家公司所有这些记录的组合，即使它们并不一起存在于 Brokers 表中。我已经执行了一个左外连接来获取你的数字，所以如果返回 NULL 值(没有相应的记录)，它会插入 0。

这可能不完全正确(例如，我不知道您如何划分月份)，但应该为您指明正确的方向。

更新

好的，如果我理解正确的话，您希望将“空”行限制为经纪人在同一市场内至少进行过一笔交易的交易类型。

我假设“BROKERS”是当前月份的 View - 所以如果表中有记录，则它发生在这个月。

所以我们可以删除交叉连接并改为这样做:

WITH m AS (
    select 9 as tran_type_cd, 'FOREX' as market_name from dual
    UNION ALL
    select 10, 'FOREX' as market_name from dual
    UNION ALL
    select 1, 'STOCKMARKET' from dual
    UNION ALL
    select 2, 'STOCKMARKET' from dual
    UNION ALL 
    SELECT 3, 'CONTRACTS' from dual
    UNION ALL 
    SELECT 4, 'CONTRACTS' from dual
)
select
    bm.company, tt.tran_type, m.market_name, 
    nvl(v.curr_month, 0) as curr_month,
    nvl(v.all_mnth, 0) as all_mnth
from
    (select distinct b.company, m.market_name
     from brokers b
     join m on m.tran_type_cd = b.tran_type_cd) bm
join
    m on m.market_name = bm.market_name
join
    TRAN_TYPE_CD_EXPL tt on tt.tran_type_cd = m.tran_type_cd
left join (
    select b.COMPANY, b.tran_type_cd, b.CURR_MONTH, b.ALL_MNTH
    from    BROKERS b
) v
on v.company = bm.company and v.tran_type_cd = m.tran_type_cd;

View “bm”(首字母缩写词的绝佳选择，我知道!)应该会为您提供当月与公司相关的独特市场列表。然后我加入市场 View 以获取与该市场相关的所有交易类型。和以前一样，如果 Brokers 表中没有匹配的记录，我使用左连接用 0 填充空行。

这是 SQL fiddle :

http://sqlfiddle.com/#!4/2f16e/13