Question

我很麻烦地把下文关于甲骨质的行文编成文。不清楚为什么——将错误改为:

ORA-905 错误——遗漏关键词

在劳动和社会保障部和劳动和社会保障部的劳动报酬。

关于如何确定标准的任何指示都将受到高度赞赏。

SELECT product.productId, product.version
 FROM product 
INNER JOIN (SELECT productId,
                   MAX(version) AS MaxVersion
              FROM product
          GROUP BY productId) AS vTbl ON product.productId= vTbl.productId 
                                     AND product.version= vTbl.MaxVersion

Answer 1

SELECT
  product.productId, product.version
FROM
    product JOIN
    (
            SELECT
                    productId,
                    MAX(version) AS MaxVersion
            FROM
                    product
            GROUP BY productId

   ) vTbl ON
                product.productId= vTbl.productId AND
                product.version= vTbl.MaxVersion

Answer 2

这只是提出质问和表明问题(Micheal回答了这一问题):

AS  VTBL

只需要

VTBL

......

WITH product AS(
 SELECT 1 productId, 2 version FROM DUAL
 union
 SELECT 2 productId, 2 version FROM DUAL
 UNION
 SELECT 3 productId, 3 version FROM DUAL
 union
 SELECT 2 productId, 6 version FROM DUAL
 UNION
 SELECT 3 productId, 4 version FROM DUAL
 UNION
 SELECT 4 productId, 5 version FROM DUAL 
)
SELECT
       PRODUCT.PRODUCTID, 
   product.version
    FROM
            PRODUCT 
    INNER JOIN
            (
                    SELECT
                            productId,
                            MAX(version) AS MaxVersion
                    FROM
                            product
                    GROUP BY productId

           ) /*as*/  VTBL ON
                        product.productId= vTbl.productId AND
                        product.version= vTbl.MaxVersion;

我倾向于明示,这样我就离开了INNER JOIN,但Michael s response是正确的(因为他删除了AS)。

俄空局投下这一错误

ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:    
*Action:

(11g r1测试)

Answer 3

其他答案是正确的,但如果你想利用Oracle,你可能希望利用分析功能。这种方法只需要一个表扫描,而不是两个表:

select productId, maxVersion
from
(
  select product.productId, version
    ,max(version) over (partition by productId) maxVersion
  from product
)
where version = maxVersion;

友情链接