时间戳

首页 > 解决方案 > 减法后如何添加新列

sql - 减法后如何添加新列

问题描述

我有一个观点。当我用 pc_no = 123 执行它时，得到这些结果

name |school   |amount
Jone |Cambridge|400
Steve|London   |400
Mark |Koln     |200
paul |Barcelona|700
Harry|Paris    |0

当我用其他 pc_no = 456 执行它时，得到这些结果

name  |school   |amount
Jone  |Cambridge|300
Steve |London   |400
Mark  |Koln     |500    
Harry |Paris    |50

我想要做的是看到这样的差异行和数量差异

name |school    |lastAmount  |FirstAmount |Diff
Jone |Cambridge |400         |300         |100
Mark |Koln      |200         |500         |-300
paul |Barcelona |700         |0           |700 
Harry|Paris     |0           |50          |-50

我执行了这个查询来获取不同的行

select a.name, a.school,a.amount lastAmount, nvl(b.amount,0) FisrstAmount, 
a.amount - b.amount Diff
from
 (
   select name,school,amount from SchemaName.ViewName  where pc_no= 123
    minus
   select name,school,amount from SchemaName.ViewName  where pc_no= 456
  )  a , SchemaName.ViewName  b where
b.invoice_no =456
and a.name=b.name
and a.school=b.school

结果：

name |school    |lastAmount  |FirstAmount |Diff
Jone |Cambridge |400         |300         |100
Mark |Koln      |200         |500         |-300
Harry|Paris     |0           |50          |-50

我没有得到只有保罗的记录。

标签： sqloracle

解决方案

您需要amount这两组的值，所以minus这不是您真正想要的。这仅显示第一个表中的值。

您可以通过将表完全外部连接在一起来获得此信息，不包括差异为零的那些行：

create table t1 (
  nm     varchar2(30),
  school varchar2(30),
  amount int
);

create table t2 (
  nm     varchar2(30),
  school varchar2(30),
  amount int
);

insert into t1 values ( 'Jone ', 'Cambridge', 300 );
insert into t1 values ( 'Steve', 'London', 400 );
insert into t1 values ( 'Mark',  'Koln', 500 );
insert into t1 values ( 'Harry', 'Paris', 50 );


insert into t2 values ('Jone ', 'Cambridge', 400 );
insert into t2 values ('Steve', 'London', 400 );
insert into t2 values ('Mark', 'Koln', 200 );
insert into t2 values ('paul', 'Barcelona', 700 );
insert into t2 values ('Harry', 'Paris', 0 );

select nm, school, 
       nvl ( t1.amount, 0 ) first_amount, 
       nvl ( t2.amount, 0 ) last_amount,  
       nvl ( t2.amount, 0 ) - nvl ( t1.amount, 0 ) diff
from   (
  select 1 tab, t1.* from t1
) t1 full outer join (
  select 2 tab, t2.* from t2
) t2
using ( nm, school )
where  nvl ( t1.amount, 0 ) - nvl ( t2.amount, 0 ) <> 0;

NM       SCHOOL       FIRST_AMOUNT   LAST_AMOUNT   DIFF   
Jone     Cambridge             300           400    100 
Mark     Koln                  500           200   -300 
paul     Barcelona               0           700    700 
Harry    Paris                  50             0    -50

推荐阅读