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
16
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
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
2
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
1
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
1
(
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
1
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
16
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
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
2
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
1
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
1
(
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
1
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
)