ORACLE SQL中的MAX() [英] MAX() in ORACLE SQL

问题描述

我有一个表，该表存储已完成的维护任务的记录列表以及完成的日期和时间.我正在尝试执行子查询以提取具有最新日期的每个任务的记录.我的SQL语句是:

I have a table that stores a list of records for Maintenance tasks that have been done and the date and time that they were done. I'm trying to do a sub-query to pull out the records for each task that has the most recent date. My SQL statement is:

    SELECT "ENGINEERING_COMPLIANCE"."EO" AS "EO",
       "ENGINEERING_COMPLIANCE"."AC" AS "AC",
       "ENGINEERING_COMPLIANCE"."PN" AS "PN",
       "ENGINEERING_COMPLIANCE"."PN_SN" AS "PN_SN",
       "ENGINEERING_COMPLIANCE"."HOURS_RESET" AS "HOURS_RESET",
       "ENGINEERING_COMPLIANCE"."MINUTES_RESET" AS "MINUTES_RESET",
       "ENGINEERING_COMPLIANCE"."CYCLES_RESET" AS "CYCLES_RESET",
       "ENGINEERING_COMPLIANCE"."RESET_DATE" AS "RESET_DATE",
       "ENGINEERING_COMPLIANCE"."RESET_HOUR" AS "RESET_HOUR",
       "ENGINEERING_COMPLIANCE"."RESET_MINUTE" AS "RESET_MINUTE",
       MAX ( "ENGINEERING_COMPLIANCE"."RESET_DATE" ) AS "LAST_COMP_DATE"
  FROM ENGINEERING_COMPLIANCE
GROUP BY ( "ENGINEERING_COMPLIANCE"."EO" ) ,
       ( "ENGINEERING_COMPLIANCE"."AC" ) ,
       ( "ENGINEERING_COMPLIANCE"."PN" ) ,
       ( "ENGINEERING_COMPLIANCE"."PN_SN" )

但是，我始终收到以下错误:"ORA-00979:不是GROUP BY表达式"

However I keep getting the following error: "ORA-00979: not a GROUP BY expression"

当我删除"GROUP BY"时，我得到: "ORA-00937:不是单组分组功能"

When I remove the "GROUP BY" then I get: "ORA-00937: not a single-group group function"

1-这到底是什么意思 2-该语句有什么问题?

1 - what exactly does that mean 2 - what is wrong with the statement?

推荐答案

您在SELECT子句中添加但没有聚合函数的列都应在GROUP BY子句中.

The columns which ever you added in the SELECT clause without an aggregate function should be in the GROUP BY clause.

为了使其更加清晰:
举个例子:

To make it little bit clear:
Take this Example:

SELECT子句中有TransactionID，AccountID，TransactionAmount，TransactionDate，并且在所有日期都需要SUM(TransactionAmount)，在这种情况下，如果您添加

You have TransactionID, AccountID, TransactionAmount, TransactionDate in your SELECT Clause, and you need SUM(TransactionAmount) on all dates, in that case, if you add

SELECT TransactionDate, TransactionID, AccountID, SUM(TransactionAmount) 
FROM Table 
GROUP BY TransactionDate

那你会得到一个错误，为什么，因为
假设您在20160101上有4笔交易，每笔交易金额为$ 1000
您对结果的期望将是

Then you will get an error, why because
Assume you have a 4 transactions on 20160101 and each transactionAmount is $1000
Your result expectation will be

TransDate      TransAmt
 20140101          4000

在这种情况下，如果将其他属性(如AccountID和TransactionID)带入SELECT子句中，它们将流向何处?这就是为什么我们必须将GROUP子句中的所有属性都包括在SELECT子句中，而AGGREGATE函数中的属性除外.

In this case, if you bring other attributes in the SELECT clause like AccountID and TransactionID, where will they go? This is why we have to include all the attributes in GROUP Clause what ever in the SELECT clause except the one which is with the AGGREGATE function.

这篇关于ORACLE SQL中的MAX()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！