SQL Oracle 到 SQL Server

debugcn 发表于 Dev

西尔代兹

此查询适用于 Oracle：

SELECT EXTRACT(YEAR FROM "DATE_OF_PUBLICATION") AS years,  
       Count("LOSS_AMOUNT_(LOCAL_CCY)") AS events,   
       Max("LOSS_AMOUNT_(LOCAL_CCY)") AS max_losses, 
       Sum("LOSS_AMOUNT_(LOCAL_CCY)") AS sum_losses 
FROM (
  select * 
  from (
     select "DATE_OF_PUBLICATION",
            "LOSS_AMOUNT_(LOCAL_CCY)" 
     FROM TEST 
     ORDER BY "LOSS_AMOUNT_(LOCAL_CCY)" DESC 
  )
) 
GROUP BY EXTRACT(YEAR FROM "DATE_OF_PUBLICATION") 
ORDER BY EXTRACT(YEAR FROM "DATE_OF_PUBLICATION")

现在，我在 SQLServer 中执行了这个查询：

SELECT DATEPART(YEAR,"DATE_OF_PUBLICATION") AS years, 
       Count("LOSS_AMOUNT_(LOCAL_CCY)") AS events,   
       Max("LOSS_AMOUNT_(LOCAL_CCY)") AS max_losses, 
       sum("LOSS_AMOUNT_(LOCAL_CCY)") AS sum_losses 
FROM (
  select * 
  from (
    select "DATE_OF_PUBLICATION",
           "LOSS_AMOUNT_(LOCAL_CCY)" 
    FROM TEST 
    ORDER BY "LOSS_AMOUNT_(LOCAL_CCY)" DESC 
  )
) 
GROUP BY DATEPART (YEAR ,"DATE_OF_PUBLICATION") 
ORDER BY DATEPART(YEAR ,"DATE_OF_PUBLICATION")

我收到此错误：

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and 
common table expressions, unless TOP, OFFSET or FOR XML is also specified.

乔治·门努蒂斯

正如错误明确指出的那样，ORDER BY在子查询中无效。

另外：在 SQL Server 中，当您使用子查询时，它必须有一个名称：

select * from ( select .....)

无效，而

select * from ( select .....) as tablename

已验证

此外，您有两个不必要的子查询级别。以下应该工作：

SELECT 
    DATEPART(YEAR , "DATE_OF_PUBLICATION") AS years, 
    Count("LOSS_AMOUNT_(LOCAL_CCY)") AS events, 
    Max("LOSS_AMOUNT_(LOCAL_CCY)") AS max_losses, 
    Sum("LOSS_AMOUNT_(LOCAL_CCY)") AS sum_losses 
FROM AAA_20180711
GROUP BY DATEPART(YEAR , "DATE_OF_PUBLICATION") 
ORDER BY DATEPART(YEAR, "DATE_OF_PUBLICATION")

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。