时间戳

首页 > 解决方案 > 如何在没有 à 分组的情况下将虚构行添加到 Oracle 中的 SQL 结果

sql - 如何在没有 à 分组的情况下将虚构行添加到 Oracle 中的 SQL 结果

问题描述

这是我的问题，我正在处理现有的大报告，他们希望我在每次数据库中的行满足条件时添加具有特定值的虚构行（假设状态 = 已取消）我简化了查询（1000 行 SQL 代码) 对此：

如果我有两个表 A 和 B ：

Table A
Name            Status              Estimated       Real  
COMPANY A       Completed           $50.00          $50.00   
COMPANY B       Canceled            $0.00           $0.00  
COMPANY C       Not Approved        $100.00     $0.00  
COMPANY D       Withdrawn           $20.00          $10.00   
COMPANY E       Not Approved        $0.00           $0.00  
COMPANY F       Canceled            $1,000.00       $1,000.00
---------------------------------------------------------------

Table B
Name            Status              Estimated       Real  
COMPANY G       In Progress         $50.00          $20.00   
COMPANY H       Not Started         $20.00          $0.00  
COMPANY H       Passed              $100.00     $100.00  
COMPANY I       Approved            $20.00          $00.00   
COMPANY J       Canceled            $14.00          $6.00  
COMPANY A       Scheduled           $2,000.00       $2,000.00

查询是这样的：

SELECT * from (
SELECT NAME,STATUS,ESTIMATED,REAL from A
UNION ALL
SELECT NAME,STATUS,ESTIMATED,REAL from B
) order by name

结果是这样的：

Name            Status              Estimated       Real  
COMPANY A       Completed           $50.00          $50.00   
COMPANY A       Scheduled           $2,000.00       $2,000.00
COMPANY B       Canceled            $0.00           $0.00  
COMPANY C       Not Approved        $100.00     $0.00  
COMPANY D       Withdrawn           $20.00          $10.00   
COMPANY E       Not Approved        $0.00           $0.00  
COMPANY F       Canceled            $1,000.00       $1,000.00
COMPANY G       In Progress         $50.00          $20.00   
COMPANY H       Not Started         $20.00          $0.00  
COMPANY H       Passed              $100.00     $100.00  
COMPANY I       Approved            $20.00          $00.00   
COMPANY J       Canceled            $14.00          $6.00

现在我需要做的是每当状态被取消时插入虚构的行：对于状态 = 已取消的每一行添加一个具有相同名称和估计列、状态计划和真实 = 0 的行。结果应该如下所示：（我在虚构的行之前添加了**）

Name            Status              Estimated       Real  
COMPANY A       Completed           $50.00          $50.00   
COMPANY A       Scheduled           $2,000.00       $2,000.00
COMPANY B       Canceled            $0.00           $0.00  
**COMPANY B     Scheduled           $0.00           $0.00**  
COMPANY C       Not Approved        $100.00     $0.00  
COMPANY D       Withdrawn           $20.00          $10.00   
COMPANY E       Not Approved        $0.00           $0.00  
COMPANY F       Canceled            $1,000.00       $1,000.00
**COMPANY F     Scheduled           $1,000.00       $0.00**
COMPANY G       In Progress         $50.00          $20.00   
COMPANY H       Not Started         $20.00          $0.00  
COMPANY H       Passed              $100.00     $100.00  
COMPANY I       Approved            $20.00          $00.00   
COMPANY J       Canceled            $14.00          $6.00  
**COMPANY J     Scheduled           $14.00          $0.00**

我尝试使用双重连接或联合，但我不知道缺少什么。十分感谢

标签： sqloraclereporting

解决方案

简单地在子查询中使用您的with查询

with tab as (
SELECT * from (
SELECT "Name","Status" ,"Estimated","Real" from A
UNION ALL
SELECT "Name","Status" ,"Estimated","Real" from B
)
)
select * from tab

UNION ALL并从同一源添加新查询，仅过滤cancelled行并根据需要管理列：

with tab as (
SELECT * from (
SELECT "Name","Status" ,"Estimated","Real" from A
UNION ALL
SELECT "Name","Status" ,"Estimated","Real" from B
)
)
select * from tab
union all
select "Name", 'Scheduled', "Estimated", 0 "Real"
from tab where "Status" = 'Canceled';

请注意，这real不是列名的最佳选择，因为它是一个保留字，所以我必须用双引号将它括起来。

推荐阅读