时间戳

首页 > 解决方案 > MySql 5.7 错误（这与 sql_mode=only_full_group_by 不兼容）

python - MySql 5.7 错误（这与 sql_mode=only_full_group_by 不兼容）

问题描述

我已经看到几个答案说修复您的查询，但我GROUP BY CONCAT的查询中有并且不知道如何修复它。

 cur.execute('''
    SELECT user_id, symbol FROM first_sweep    
    WHERE date_time BETWEEN %s AND %s AND action = 'buy'
    GROUP BY CONCAT(user_id, "-",symbol) 
    ''', (min_rng, max_rng,))    
    fs = cur.fetchall()

我得到的完整错误是：

(1055, "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stockpicker.first_sweep.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

我正在使用 Ubuntu 18.04、MySql 5.7 和 Python 2.7

标签： pythonmysqlgroup-by

解决方案

这有效：

SELECT 
  CONCAT(user_id, '-', symbol) as usym, 
  MAX(user_id) AS user_id, 
  MAX(symbol) AS symbol
FROM first_sweep    
WHERE date_time BETWEEN %s AND %s AND action = 'buy'
GROUP BY usym

推荐阅读