mysql - SQL INSERT INTO SELECT 语句组函数使用无效

Question

我有以下查询:

INSERT INTO StatisticalConsultationAgreement VALUES (
   queryType, entityCode, entityType, queryClass,queryTables,period, 
   COUNT(queryClass),  SUM(numberRecords),  SUM(recordsFound),
   SUM(NorecordsFound), NOW(), 'system');
SELECT
   MONTH(EndDateTimeProcessing),YEAR(EndDateTimeProcessing),
   entityType,
   entityCode,
   queryType,
   queryClass,
   EndDateTimeProcessing as period
FROM agreementFile
WHERE 
  MONTH(EndDateTimeProcessing)=MONTH(DATE_SUB( CURDATE(), INTERVAL 1 MONTH )) 
  AND YEAR(EndDateTimeProcessing)=YEAR(CURDATE())
GROUP BY entityType,entitycode,queryType, queryClass;

当我运行查询时，出现下一个错误:

Error code 1111, SQL state HY000: Invalid use of group function
Line 1, column 1

Executed successfully in 0,002 s.
Line 5, column 2

为什么会出现这种情况？

如何解决？

Answer 1

您在 insert 中将 values 语句与 select 语句混合在一起。您只需要选择。这是我对你想要的最好的猜测:

INSERT INTO StatisticalConsultationAgreement 
    SELECT queryType, entityCode, entityType, queryClass,queryTables,period, 
           COUNT(queryClass),  SUM(numberRecords),  SUM(recordsFound),
           SUM(NorecordsFound), NOW(), 'system'
    FROM agreementFile
    WHERE MONTH(EndDateTimeProcessing)=MONTH(DATE_SUB( CURDATE(), INTERVAL 1 MONTH )) AND
          YEAR(EndDateTimeProcessing)=YEAR(CURDATE())
    GROUP BY entityType, entitycode, queryType, queryClass;

但是，您还应该在 insert 语句中列出 StatisticalConsultationAgreement 的列名称。