python - MySql 5.7 错误(这与 sql_mode=only_full_group_by 不兼容)

Question

我看到几个回答说修复您的查询，但我的查询中有 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

Answer 1

这个有效:

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