MySQL 错误 1241 (21000) : Operand should contain 1 column(s) on Aggregate Query

Question

我使用的是 MySQL Ver 14.14 Distrib 5.5.52，适用于 debian-linux-gnu (armv7l)，使用在 RaspberyPI 3 上运行的 raspbian 操作系统上的 readline 6.3。

我运行以下查询并得到以下结果。

mysql> SELECT
    -> sensorMeasurements.sensorMeasurementDate,
    -> MIN(sensorMeasurements.sensormeasurementvalue) as "minValue",
    -> MAX(sensorMeasurements.sensormeasurementvalue) as "maxValue",
    -> AVG(sensorMeasurements.sensormeasurementvalue) as "avgValue"
    -> FROM   sensorMeasurements
    ->        INNER JOIN userSystemSensors
    ->                ON sensorMeasurements.usersystemsensorid =
    ->                   userSystemSensors.usersystemsensorid
    ->        INNER JOIN sensors
    ->                ON userSystemSensors.sensorid = sensors.sensorid
    ->        INNER JOIN userSystems
    ->                ON userSystemSensors.usersystemid = userSystems.usersystemid
    ->        INNER JOIN users
    ->                ON userSystems.userid = users.userid
    -> WHERE  sensors.sensorid = 1
    ->        AND sensorMeasurements.sensormeasurementdatetime BETWEEN
    ->            "2016-11-05 10:00:00" AND "2016-11-15 11:00:00"
    -> GROUP BY (sensorMeasurements.sensorMeasurementDate);

+-----------------------+----------+----------+-------------------+ 
| sensorMeasurementDate | minValue | maxValue | avgValue          |
+-----------------------+----------+----------+-------------------+ 
| 2016-11-05            |     66.9 |       73 | 69.56107784431138 | 
| 2016-11-06            |     51.5 |     73.4 | 64.03751600512162 | 
| 2016-11-07            |     40.3 |     68.7 | 58.34236111111102 | 
| 2016-11-08            |     39.2 |     59.4 |  48.1092361111111 | 
| 2016-11-09            |     13.2 |     54.8 | 36.78529850746269 |
+-----------------------+----------+----------+-------------------+

当我将该查询修改为以下内容时，出现错误错误 1241 (21000):操作数应包含 1 列

SELECT 
sensors.sensorName,
sensorMeasurements.sensorMeasurementDate,
MIN(sensorMeasurements.sensormeasurementvalue) as "minValue",
MAX(sensorMeasurements.sensormeasurementvalue) as "maxValue",
AVG(sensorMeasurements.sensormeasurementvalue) as "avgValue"
FROM   sensorMeasurements 
       INNER JOIN userSystemSensors 
               ON sensorMeasurements.usersystemsensorid = 
                  userSystemSensors.usersystemsensorid 
       INNER JOIN sensors 
               ON userSystemSensors.sensorid = sensors.sensorid 
       INNER JOIN userSystems 
               ON userSystemSensors.usersystemid = userSystems.usersystemid 
       INNER JOIN users 
               ON userSystems.userid = users.userid 
WHERE  sensors.sensorid = 1 
       AND sensorMeasurements.sensormeasurementdatetime BETWEEN 
           "2016-11-05 10:00:00" AND "2016-11-15 11:00:00"
GROUP BY (sensors.sensorName, sensorMeasurements.sensorMeasurementDate);

我忽略了什么想法吗？

谢谢史蒂夫

Answer 1

Mihai's answer工作过:

Remove the () from your GROUP BY