mysql - 唯一字段上的group by的Sql问题

Question

我的sql看起来像这样：

create table user(
  id varchar(45),
  name varchar(45),
  comment varchar(10)
  );
  
insert into user(id, name, comment)
values
('357607425559331', 'Name1', 'Comment'),
('240358944458028', 'Name2', 'Comment'),
('173956187882837', 'Name3', 'Comment'),
('4174662639277087', 'Name4', 'Comment'),
('1992571284232684', 'Name5', 'Comment'),
('285647222973252', 'Name6', 'Comment'),
('263639542044766', 'Name7', 'Comment'),
('483596959315457', 'Name8', 'Comment'),
('893514004824380', 'Name9', 'Comment'),
('852595485297071', 'Name10', 'Comment'),
('772471550150585', 'Name11', 'Comment');

create table insights(
  user_id varchar(45),
  date_time datetime
  );
  
insert into insights(user_id, date_time)
values
('357607425559331', '2021-04-03'),
('240358944458028', '2021-04-03'),
('173956187882837', '2021-04-03'),
('4174662639277087', '2021-04-03'),
('1992571284232684', '2021-04-03'),
('285647222973252', '2021-03-03'),
('263639542044766', '2021-04-03'),
('483596959315457', '2021-04-03'),
('893514004824380', '2021-04-03'),
('852595485297071', '2021-04-03'),
('772471550150585', '2021-04-03'),
('357607425559331', '2021-04-02'),
('240358944458028', '2021-04-02'),
('173956187882837', '2021-04-02'),
('4174662639277087', '2021-04-02'),
('1992571284232684', '2021-04-02'),
('285647222973252', '2021-04-02'),
('263639542044766', '2021-04-02'),
('483596959315457', '2021-04-02'),
('893514004824380', '2021-04-02'),
('852595485297071', '2021-04-02'),
('772471550150585', '2021-04-02'),
('357607425559331', '2021-04-01'),
('240358944458028', '2021-04-01'),
('173956187882837', '2021-04-01'),
('4174662639277087', '2021-04-01'),
('1992571284232684', '2021-04-01'),
('285647222973252', '2021-04-01'),
('263639542044766', '2021-04-01'),
('483596959315457', '2021-04-01'),
('893514004824380', '2021-04-01'),
('852595485297071', '2021-04-01'),
('772471550150585', '2021-04-01'),
('357607425559331', '2021-03-31'),
('240358944458028', '2021-03-31'),
('173956187882837', '2021-03-31'),
('4174662639277087', '2021-03-31'),
('1992571284232684', '2021-03-31'),
('285647222973252', '2021-03-31'),
('263639542044766', '2021-03-31'),
('483596959315457', '2021-03-31'),
('893514004824380', '2021-03-31'),
('852595485297071', '2021-03-31'),
('772471550150585', '2021-03-31');

我的查询如下所示：

SELECT user.id as user_id, user.name as user_name 
FROM user 
LEFT JOIN insights ON user.id = insights.user_id 
WHERE (insights.date_time >= '2021-03-05') AND (insights.date_time <= '2021-04-05') 
GROUP BY user.id;

运行此查询后，我收到以下错误：

Query Error: Error: ER_WRONG_FIELD_WITH_GROUP: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

为什么会出现这个错误？我的意思是该user_id字段在用户表中是唯一的，所以它不应该显示这个结果。

在另一个工作表上，这些数据一定有问题，但我想不通。

标签： mysqlsql