oracle sql怎么写:

发布网友发布时间：2022-06-03 22:53

共1个回答

热心网友时间：2023-10-25 11:02

创建表，插入数据

create table a
(id int,
num int);

create table b
(id int,
num int,
time date);

insert into a values (1,50);
insert into a values (2,44);

insert into b values (1,8,to_date('20170329','yyyymmdd'));
insert into b values (4,66,to_date('20160101','yyyymmdd'));
insert into b values (1,2,to_date('20160303','yyyymmdd'));
commit;

执行：

select a.id,
       case
         when a.id = b.id then
          a.num - b.num
         else
          a.num
       end
  from a
  left join (select * from b where to_char(b.time, 'yyyymmdd') < '20170101') b
    on a.id = b.id

结果：

追问还需要求b表中的一个Id的num的和，例如b中还有一条数据 1 ，2 ，2016/4/4
这样最终的num就等于a中Num - b中时间范围内的num的和，最终Num = a中num - b中2条符合条件的Num

追答

这样？

select a.id,
       case
         when a.id = b.id then
          a.num - b.num
         else
          a.num
       end
  from a
  left join (select id,sum(num) num from b where to_char(b.time, 'yyyymmdd') < '20170101' group by id) b
    on a.id = b.id