ORACLE SQL从2个select语句中获取两个值的差异

We have 2 very simple SELECT statements:

我们有2个非常简单的SELECT语句:

SELECT value from table where date between DATE1 and DATE2
SELECT value from table where date between DATE3 and DATE4

We need a way to write a single SQL statement which will get the difference of the "value" that is returned from these two SQL statements.

我们需要一种方法来编写单个SQL语句,它将获得从这两个SQL语句返回的“值”的差异。

We tried the following but it was not successful:

我们尝试了以下但是没有成功:

SELECT value from table where date between DATE1 and DATE2
minus 
SELECT value from table where date between DATE3 and DATE4

Any suggestion is highly appreciated.

任何建议都非常感谢。

6 个解决方案

#1

Untested but should work:

未经测试但应该工作:

SELECT
    (SELECT value from table where date between DATE1 and DATE2) - 
    (SELECT value from table where date between DATE3 and DATE4)
FROM dual;

Assuming that your SELECT value is guaranteed to return a single value

假设您的SELECT值保证返回单个值

#2

SELECT value FROM TBL WHERE date BETWEEN DATE1 and DATE2 
AND value NOT IN (SELECT value FROM TBL WHERE date BETWEEN DATE3 AND DATE4)

#3

If you think your inner queries can give multiple values, use below

如果您认为您的内部查询可以提供多个值,请使用以下内容

SELECT
(SELECT sum(value) from table where date between DATE1 and DATE2) - 
(SELECT sum(value) from table where date between DATE3 and DATE4) as answer

FROM dual;

#4

Please try this:

请试试这个:

SET SERVEROUTPUT ON
DECLARE
V_FIRST NUMBER := 0;
V_SECOND NUMBER := 0;
BEGIN
  SELECT value into V_FIRST from table where rownum=1 and date between DATE1 and DATE2;
  SELECT value into V_SECOND from table where rownum=1 and date between DATE3 and DATE4;

  DBMS_OUTPUT.PUT_LINE (V_FIRST-V_SECOND); 
END;

#5

(
SELECT value from table where date between DATE1 and DATE2
 minus 
SELECT value from table where date between DATE3 and DATE4
)
union all
(
SELECT value from table where date between DATE3 and DATE4
 minus 
SELECT value from table where date between DATE1 and DATE2
)

#6

Below statement should work for your case

以下声明应适合您的情况

(
SELECT value from table where date between DATE3 and DATE4
 minus 
SELECT value from table where date between DATE1 and DATE2
)
union
(
SELECT value from table where date between DATE1 and DATE2
 minus 
SELECT value from table where date between DATE3 and DATE4
)

#1