Question

我愿取得这样的成就:

SELECT col1,col2-(SELECT foo FROM table1) 
FROM table2 
WHERE col2>(SELECT foo FROM table1)

如无SlectTing foo两次。

Can I do that with Oracle SQL? Would it be more efficient than SELECTing twice (or multiple times)?

Answer 1

How about

WITH
  foo      AS  select foo from table1
SELECT
    col1, col2 - foo.foo
FROM
    table2, foo
WHERE
   col2 > foo.foo

Answer 2

You could write it like this:

SELECT
    s.col1, s.col2 - s.foo
FROM
(
    SELECT col1, col2, (SELECT foo FROM table1) as foo
    FROM table2
) s
WHERE s.col2 > s.foo

Answer 3

假设您的舱位回归单行(否则,该子舱的两种用途都会回落差错),则只有一位喀里士加入。

SELECT a.col1, a.col2 - b.foo
  FROM table2 a,
       (SELECT foo FROM table1) b
 WHERE a.col2 > b.foo

Answer 4

SELECT col1, col2 - foo
  FROM table2, table1
 WHERE col2 > foo

但只有表1有一行。