ORACLE SQL中的MAX()

Question

我有一个表，用于存储已完成的维护任务的记录列表以及完成这些任务的日期和时间。我试图做一个子查询，为每一个有最近日期的任务提取记录。我的SQL语句是：

    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“时，我得到："ORA-00937:不是单个组函数”。

这到底是什么意思2--这句话有什么问题？

Answer 1

在没有聚合函数的SELECT子句中添加的列应该在GROUP子句中。

让我们说清楚一点：

举个例子：

选择条款中有TransactionID、AccountID、TransactionAmount、TransactionDate，如果添加，则在所有日期都需要SUM(TransactionAmount)。

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

那么您将得到一个错误，为什么

假设您在20160101上有4个事务，并且每个transactionAmount是$1000

你的预期结果是

TransDate      TransAmt
 20140101          4000

在这种情况下，如果在SELECT子句中引入其他属性(如AccountID和TransactionID )，它们将去哪里？这就是为什么我们必须将组子句中的所有属性包括在SELECT子句中，除了带有聚合函数的属性之外。

Answer 2

应该将组中的所有列设置为

 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" , 
   "ENGINEERING_COMPLIANCE"."HOURS_RESET" ,
   "ENGINEERING_COMPLIANCE"."MINUTES_RESET" ,
   "ENGINEERING_COMPLIANCE"."CYCLES_RESET" ,
   "ENGINEERING_COMPLIANCE"."RESET_DATE",
   "ENGINEERING_COMPLIANCE"."RESET_HOUR" ,
   "ENGINEERING_COMPLIANCE"."RESET_MINUTE"

Answer 3

我根本不知道MySQL，但是在Oracle中，您需要按select列表中列出的所有非聚合功能列进行分组。

像这样的事情应该有效：

 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", 
          "engineering_compliance"."hours_reset", 
          "engineering_compliance"."minutes_reset", 
          "engineering_compliance"."cycles_reset", 
          "engineering_compliance"."reset_date", 
          "engineering_compliance"."reset_hour", 
          "engineering_compliance"."reset_minute";