MySQL 到 Oracle 语法错误（Limit / Offset / Update）

Question

我有一个在当前MySQL数据库上运行的查询。现在我被迫转移到Oracle，因此我正在尝试将所有存储过程/程序移植到使用Oracle SQL语法。我在一个特定的查询上遇到了很多麻烦。以下是MySQL查询。它使用子查询更新表。

 update table1 alf 
 set nextcontractid = 
 (
 select 
    contractid from table1copy alf2 
 where 
    alf2.assetid = alf.AssetID 
 and 
    alf2.lasttradedate > alf.LastTradeDate 
 order by lasttradedate asc limit 1
 ) 
 where complete = 0

在Oracle中，我无法使用limit命令，因此我寻找了解决方法。这是我的Oracle查询。（但它不起作用。）

 update table1 alf
 set nextcontractid = 
   (select contractid from
     (
     SELECT contractid, rownum as row_number
     FROM table1copy alf2
     WHERE alf2.assetid     = alf.assetid
     AND alf2.lasttradedate > alf.lasttradedate
     ORDER BY lasttradedate ASC
   ) 
   where row_number = 1)
 where alf.complete = 0

我遇到了以下错误：

 Error at Command Line:8 Column:29
 Error report:
 SQL Error: ORA-00904: "ALF"."LASTTRADEDATE": invalid identifier
 00904. 00000 -  "%s: invalid identifier"

第8行是：

 AND alf2.lasttradedate > alf.lasttradedate

移除UPDATE语句并在子查询中插入一些虚拟值可使子查询的结果正确：

   (select contractid from 
     ( 
     SELECT contractid, rownum as row_number 
     FROM asset_list_futures_copy alf2 
     WHERE alf2.assetid     = 'GOLD' 
     AND alf2.lasttradedate > '20110101' 
     ORDER BY lasttradedate ASC 
     )  
    where row_number = 1)

看到这个错误，似乎是对alf的第二个引用没有起作用。有什么想法可以改变查询语句，使其在oracle中起作用吗？

- Brian

---

不是百分之百确定，我在想：SELECT contractid, rownum as row_number FROM asset_list_futures_copy alf2 WHERE alf2.assetid = 'GOLD' AND alf2.lasttradedate > '20110101' HAVING ROWNUM = 1 ORDER BY lasttradedate ASC - Johan

我没有按任何东西进行分组。当你按某些东西进行分组时，使用“Having”关键字是正确的吗？（http://psoug.org/reference/group_by.html） 我认为问题在于我在更新中有2个嵌套查询（外部查询仅从内部查询中选择顶部条目，该内部查询选择一堆数据并对其进行排序），而内部查询不知道 alf 是什么。 - Brian

Answer 1

看起来解析器不喜欢这样，尽管它在语法上是正确的。可能是因为两个嵌套和有序的从句让它感到困惑了。我已经复制了这个问题。

您可以使用分析函数：

update table1 alf
 set nextcontractid = 
     (SELECT min(contractid) keep (dense_rank first order by lasttradedate asc) 
     FROM table1copy alf2
     WHERE alf2.assetid     = alf.assetid
     AND alf2.lasttradedate > alf.lasttradedate
     )
 where alf.complete = 0

- Florin Ghita

Answer 2

在需要返回多个结果的情况下，你可以使用 WHERE rownum = 1 或者 rownum BETWEEN x AND y。

- Narf

---

如果我不使用两个嵌套的查询，那么这个方法可以奏效。然而，这种方法只在升序排列时有效（我猜我的示例不够好）。如果你想要降序排列，你需要获取max(rownum)，这就需要两个嵌套的查询，而且同样的问题又会出现。 - Brian

确实，Oracle在这方面做得很糟糕。但据我所知，这是唯一的LIMIT替代方案，而额外的嵌套是非常常见的。 - Narf