我有一个表,用于存储已完成的维护任务的记录列表以及完成它们的日期和时间.我正在尝试执行子查询以提取具有最新日期的每个任务的记录.我的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表达式”
当我删除“GROUP BY”时,我得到：
“ORA-00937：不是单组组功能”
1 – 究竟是什么意思
2 – 声明有什么问题？
解决方法
您在SELECT子句中添加的没有聚合函数的列应该在GROUP BY子句中.
为了使它有点清楚：
拿这个例子：
您的SELECT子句中有TransactionID,AccountID,TransactionAmount,TransactionDate,并且在所有日期都需要SUM(TransactionAmount),在这种情况下,如果添加
SELECT TransactionDate,TransactionID,SUM(TransactionAmount) FROM Table GROUP BY TransactionDate
然后你会得到一个错误,为什么呢
假设您在20160101上有4笔交易,每笔交易额为1000美元
你的结果期望是
TransDate TransAmt 20140101 4000
在这种情况下,如果您在SELECT子句中引入其他属性,如AccountID和TransactionID,它们将在哪里？这就是为什么我们必须在SELECT子句中包含GROUP子句中的所有属性,除了具有AGGREGATE函数的属性.
