sql - 如何使用 SQL 减去值

Question

你能告诉我如何减去值吗？

SQL>

select SUM(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='UNDOTBS1'

2 ;

SUM(BYTES/1024/1024)
--------------------
            7000

SQL>

select SUM(BYTES/1024/1024) from DBA_UNDO_EXTENTS where STATUS LIKE 'ACTIVE';

SUM(BYTES/1024/1024)
--------------------
               8

我需要得到一个值 7000 - 8

当我这样做

select SUM(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='UNDOTBS1'
minus
select SUM(BYTES/1024/1024) from DBA_UNDO_EXTENTS where STATUS='ACTIVE';

我只从第一个选择中得到结果。

Answer 1

在这里使用对偶伪表并将总和计算为标量:

SELECT 
   (SELECT SUM(bytes/1024/1024) as sum_a 
    from dba_data_files where TABLESPACE_NAME='UNDOTBS1')
 - (select SUM(BYTES/1024/1024) as sum_b 
    from DBA_UNDO_EXTENTS where STATUS LIKE 'ACTIVE') as Difference
FROM
    dual;