使用方程进行查询

Question

我有3个查询返回3个值。我想连接查询以执行以下表达式：

(MgO + CaO)/SiO2

我该怎么做呢？

MgO：

SELECT sampled_date, result 
FROM AF_VW WHERE element = 'MgO' AND ROWNUM = 1 ORDER BY sampled_date DESC;

CaO：

SELECT sampled_date, result 
FROM AF_VW WHERE element = 'CaO' AND ROWNUM = 1 ORDER BY sampled_date DESC;

SiO2：

SELECT sampled_date, result 
FROM AF_VW WHERE element = 'SiO2' AND ROWNUM = 1 ORDER BY sampled_date DESC;

Answer 1

with x as (
SELECT sampled_date, result, element,
row_number() over(partition by element order by sampled_date desc) rn 
FROM AF_VW)
, y as (
select 
case when element = 'MgO' then result end as MGO,
case when element = 'CaO' then result end as CaO,
case when element = 'SiO2' then result end as SiO2
FROM x where rn = 1)
select (mgo+cao)/sio2 from y; 

您可以使用row_number函数代替rownum，然后选择这3个元素的结果。

Answer 2

这对于评论来说有点长了。

您的问题中的查询可能没有执行您预期的操作。Oracle在order by之前计算WHERE子句。因此，下面的代码使用MgO选择一行，然后按日期对一行进行简单的排序：

SELECT sampled_date, result 
FROM AF_VW
WHERE element = 'MgO' AND ROWNUM = 1
ORDER BY sampled_date DESC;

实际上，要获得相同的结果，您需要模拟相同的、不稳定的逻辑。不稳定，因为如果多次运行查询，不能保证结果是相同的：

with mg as (
          SELECT sampled_date, result 
          FROM AF_VW
          WHERE element = 'MgO' AND ROWNUM = 1
         ),
     coa as (
          SELECT sampled_date, result 
          FROM AF_VW
          WHERE element = 'CaO' AND ROWNUM = 1
         ),
     sio2 as (
          SELECT sampled_date, result 
          FROM AF_VW
          WHERE element = 'SiO2' AND ROWNUM = 1
     )
select (mgo.result + cao.result) / sio2.result
from mgo cross join cao cross join sio2;

我怀疑你真的想要最新的样本日期，这就是VKP的答案所提供的。我只是想你应该知道这不是你当前的查询所做的事情。