时间戳

首页 > 解决方案 > plsql减去同一张表中的列

sql - plsql减去同一张表中的列

问题描述

我有一个简单的学生表。

name  | amount | vdate
Josh  | 15     | 01.01.2020
Steve | 25     | 05.04.2008
Josh  | 40     | 01.01.2022

我想要做的是彼此减去 Josh 值。

我写了这个查询，但它不起作用

   select name , sum(b.amount-a.amount) diff from  
     select name,amount from student a where name = 'Josh' and vdate='01.01.2020'
     union all
     select name,amount from student b where name = 'Josh' and vdate = '01.01.2022')
   group by name

预期结果是：

name | diff
Josh | 25
Steve| 25

标签： sqlplsql

解决方案

你可以试试这段代码，

    select 
    fname,
    abs(sum(amount2)) amount
from
(
WITH
student(fname,amount,vdate) AS (
            SELECT 'Josh' ,15, to_date('01102017','ddmmyyyy')  from dual
  UNION ALL SELECT 'Steve',25,  to_date('01102017','ddmmyyyy') from dual
  UNION ALL SELECT 'Josh' ,40   ,to_date('01102019','ddmmyyyy')from dual
)


select 
    h.fname,
    h.amount,
    decode((ROW_NUMBER() OVER(PARTITION BY fname order by vdate desc)),1,amount,amount* -1) amount2

    from student h
)
group by 
    fname
    ;

我假设您获得的人的金额更大并减去其他值，您可以通过修改分区窗口中的 order by 子句来选择更大的日期，即

decode((ROW_NUMBER() OVER(PARTITION BY fname order by vdate desc)),1,amount,amount * -1) amount2

推荐阅读