sql - Apache Derby分组或聚合错误:

Question

我的Apache Derby问题(带有子查询和内部联接)存在以下问题:

SELECT * FROM (SELECT ID,ENTRYDATE,CAST((SUM(DRUGAMOUNT_ML)) AS DECIMAL(15,2)) AS SUM_DRUGAMOUNT_ML FROM PORPHYRIE GROUP BY ENTRYDATE) a 
INNER JOIN PORPHYRIE b
ON a.ID=b.ID
;

该表PORPHYRIE看起来像:

  ID   EntryDate   DRUGAMOUNT_ML DRUGAMOUNT_MG ....

  6601 2014-11-15  0.25 ...
  6602 2014-11-15  0.26 ...
              .
              .
              .

错误消息是:

Column reference 'PORPHYRIE.ID' is invalid. For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.

我不明白问题是什么..请帮助!

Answer 1

我自己解决了问题:

正确的解决方案是:

SELECT * FROM (SELECT ENTRYDATE,CAST((SUM(DRUGAMOUNT_ML)) AS DECIMAL(15,2)) AS SUM_DRUGAMOUNT_ML FROM PORPHYRIE GROUP BY ENTRYDATE) a 
INNER JOIN PORPHYRIE b
ON a.ENTRYDATE=b.ENTRYDATE
;

如果在derby中执行分组或聚合功能，则所有列都必须是分组或聚合操作的一部分。因此，必须消除子查询中的ID列，并且必须在两个表的ENTRYDATES-Columns上进行内部联接。

我认为这不是一个非常聪明的解决方案。