SQL 中两个查询之间的差异（Oracle Apex 项目）

Question

我在 APEX 应用程序的不同项目中有以下两个查询：

---

ITEM 1:

SELECT SUM(t.gems)
  FROM (SELECT gems
          FROM tasks
         UNION ALL
        SELECT gems
          FROM quests
         UNION ALL
        SELECT gems
          FROM daily_quests
         ) t

---

ITEM 2:

SELECT SUM(price) FROM items

---

它们都正确返回一个数字，但我想在一个语句中减去它们。我尝试使用

SELECT TO_NUMBER(ITEM1)-TO_NUMBER(ITEM2) FROM DUAL

但没有成功。

您有什么建议吗？我对 APEX 和 SQL 有点陌生。

先感谢您。

Answer 1

假设每个查询生成一个名为 gems 的列，您可以执行以下操作：

with
a as (
  -- query 1 here
),
b as (
  -- query 2 here
)
select a.gems - b.gems as diff
from a
cross join b

Answer 2

Koen 的答案给出了此类问题的一般正确答案。

但是，在您的特定实例中，您可以使用更简单的查询：

SELECT SUM(t.gems)
FROM (SELECT gems FROM tasks
      UNION ALL
      SELECT gems FROM quests
      UNION ALL
      SELECT gems FROM daily_quests
      UNION ALL
      SELECT price * -1 FROM items
) t

Answer 3

对每个查询使用 CTE（公共表表达式），然后CROSS JOIN 它们。

WITH t_gems (sum_gems) AS
(
SELECT SUM(t.gems)
  FROM (SELECT gems
          FROM tasks
         UNION ALL
        SELECT gems
          FROM quests
         UNION ALL
        SELECT gems
          FROM daily_quests
         ) t
), t_items (sum_price) AS
(
  SELECT SUM(price) FROM items
)
SELECT t_gems.sum_gems - t_item.sum_price 
  FROM t_gems CROSS JOIN t_items