python - 使用 MySQL db 和 python 客户端执行 sql 查询时出错

Question

我有一个复杂的 sql 查询，如下所示，我使用它从 python 脚本访问 MySQL db。

 sql_query_vav = """SELECT t1.deviceId, t1.date, t1.vavId, t1.timestamp, t1.nvo_airflow as airflow, t1.nvo_air_damper_position as damper_position , t1.nvo_temperature_sensor_pps as vavTemperature , d.MILO as miloId ,m1.timestamp as miloTimestamp, m1.temperature as miloTemperature
                   FROM
                       (SELECT deviceId, date, nvo_airflow, nvo_air_damper_position, nvo_temperature_sensor_pps, vavId, timestamp, counter from vavData where date=%s and floor=%s) t1
                   INNER JOIN
                        (SELECT date,max(timestamp) as timestamp,vavId from vavData where date=%s and floor=%s group by vavId) t2
                   ON (t1.timestamp = t2.timestamp)     
                   INNER JOIN 
                       (SELECT VAV,MILO,floor from VavMiloMapping where floor = %s) d
                   ON (t1.vavId = d.VAV )
                   INNER JOIN
                         (SELECT t1.deviceId,t1.date,t1.timestamp,t1.humidity,t1.temperature,t1.block,t1.floor,t1.location
                         FROM
                             (SELECT deviceId,date,timestamp,humidity,temperature,block,floor,location from miloData WHERE date=%s and floor=%s) t1
                         INNER JOIN
                             (SELECT deviceId,max(timestamp) as timestamp,location from miloData where date=%s and floor=%s GROUP BY deviceId) t2
                         ON (t1.timestamp = t2.timestamp)) m1
                   ON (d.MILO = m1.location)  order by t1.vavId"""

我收到上述查询的错误，上面写着

mysql.connector.errors.ProgrammingError: 1055 (42000): Expression #3 of SELECT list is not in GROUP BY 
clause and contains nonaggregated column 'minniedb.miloData.location' which is not functionally dependent
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 

我试图通过执行来改变sql模式

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

并尝试使用重新启动mysql服务

sudo service mysql restart

我想我已经做了所有需要的事情，为什么我不确定为什么我仍然会遇到同样的错误。有人可以帮我解决这个问题。

标签： pythonmysqlsqlsql-mode

Answer 1

如果你想找到不正确的地方，你至少必须仔细格式化代码。

SELECT t1.deviceId, 
       t1.date, 
       t1.vavId, 
       t1.timestamp, 
       t1.nvo_airflow as airflow, 
       t1.nvo_air_damper_position as damper_position , 
       t1.nvo_temperature_sensor_pps as vavTemperature , 
       d.MILO as miloId ,
       m1.timestamp as miloTimestamp, 
       m1.temperature as miloTemperature
FROM ( SELECT deviceId, 
              date, 
              nvo_airflow, 
              nvo_air_damper_position, 
              nvo_temperature_sensor_pps, 
              vavId, 
              timestamp, 
              counter 
       from vavData 
       where date=%s 
         and floor=%s
     ) t1
INNER JOIN ( SELECT date,
                    max(timestamp) as timestamp,
                    vavId 
             from vavData 
             where date=%s 
               and floor=%s 
             group by vavId
           ) t2 ON (t1.timestamp = t2.timestamp)     
INNER JOIN ( SELECT VAV,
                    MILO,
                    floor 
             from VavMiloMapping 
             where floor = %s 
           ) d ON (t1.vavId = d.VAV )
INNER JOIN ( SELECT t1.deviceId,
                    t1.date,
                    t1.timestamp,
                    t1.humidity,
                    t1.temperature,
                    t1.block,
                    t1.floor,
                    t1.location
             FROM ( SELECT deviceId,
                           date,
                           timestamp,
                           humidity,
                           temperature,
                           block,
                           floor,
                           location 
                    from miloData 
                    WHERE date=%s 
                      and floor=%s
                  ) t1
             INNER JOIN ( SELECT deviceId,
                                 max(timestamp) as timestamp,
                                 location 
                          from miloData 
                          where date=%s 
                            and floor=%s 
                          GROUP BY deviceId
                        ) t2 ON (t1.timestamp = t2.timestamp)
           ) m1 ON (d.MILO = m1.location)  
order by t1.vavId

现在可以看出有 2 点不正确。两个有问题的子查询都有一个别名t2，看起来像

SELECT some_Id,
       max(timestamp) as timestamp,
       some_another_field
from some_table
where some_conditions
GROUP BY some_Id

标记为的字段some_another_field既不包含在 GROUP BY 表达式中，也不包含在聚合函数中。

更正这些子查询。