两个值的Oracle SQL GET差从2个select语句检索(ORACLE SQL get di

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

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

我们需要一种方法来写一个SQL语句，将获得“价值”，也就是从这两个SQL语句返回的差异。

我们试过以下，但没有成功：

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

任何的建议是高度赞赏。

Answer 1:

未经测试，但应该工作：

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

假设你的SELECT value是保证返回一个值

Answer 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)

Answer 3:

如果你觉得你内心的疑问可以给多个值，使用以下

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;

Answer 4:

请试试这个：

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;

Answer 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
)

Answer 6:

下面的语句应该适用于你的情况

(
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
)

文章来源: ORACLE SQL get difference of two values retrieved from 2 select statements