09:24:16 SELECT name, 80 - tot_cred AS credits_left FROM student WHERE dept_name = 'Comp. Sci.' GROUP BY name HAVING credits_left > 0 ORDER BY credits_left ASC LIMIT 0, 10 Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lab.student.tot_cred' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.000 sec

这个错误是由于在MySQL的新版本中，如果使用了`GROUP BY`语句，那么在`SELECT`语句中不能出现未被聚合的列。

你可以通过将`tot_cred`列聚合一下，或者将其加入到`GROUP BY`语句中来解决这个问题。

以下是修改后的MySQL语句：

SELECT name, 80 - SUM(tot_cred) AS credits_left
FROM student
WHERE dept_name = 'Comp. Sci.'
GROUP BY name
HAVING credits_left > 0
ORDER BY credits_left ASC
LIMIT 0, 10;

在这个语句中，我们使用了`SUM`函数将`tot_cred`列聚合起来，以便在`SELECT`语句中使用。

同时，我们也将`name`列加入到`GROUP BY`语句中。

这个修改后的语句应该可以正确地返回结果了。

相关问题

给出100条sql插入语句，插入表student，有ID，name，dept_name, tot_cred四个列，ID为五位int类型，tot_cred 为学分大于零小于150

以下是100条SQL插入语句，插入表student，有ID，name，dept_name, tot_cred四个列，其中ID为五位int类型，tot_cred 为学分大于零小于150：

INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10001, 'John Smith', 'Comp. Sci.', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10002, 'Jane Doe', 'Comp. Sci.', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10003, 'Bob Johnson', 'Comp. Sci.', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10004, 'Mary Lee', 'Comp. Sci.', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10005, 'Tom Brown', 'Comp. Sci.', 90);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10006, 'Sue Davis', 'Comp. Sci.', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10007, 'Jim Wilson', 'Comp. Sci.', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10008, 'Carol Smith', 'Comp. Sci.', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10009, 'David Miller', 'Comp. Sci.', 80);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10010, 'Linda Johnson', 'Comp. Sci.', 95);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10011, 'Mike Brown', 'Comp. Sci.', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10012, 'Amy Lee', 'Comp. Sci.', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10013, 'Jack Davis', 'Comp. Sci.', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10014, 'Jill Wilson', 'Comp. Sci.', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10015, 'Sam Smith', 'Comp. Sci.', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10016, 'Janet Johnson', 'Comp. Sci.', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10017, 'Bob Brown', 'Math', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10018, 'Mary Davis', 'Math', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10019, 'Tom Wilson', 'Math', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10020, 'Sue Smith', 'Math', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10021, 'Jim Lee', 'Math', 90);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10022, 'Carol Johnson', 'Math', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10023, 'David Brown', 'Math', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10024, 'Linda Davis', 'Math', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10025, 'Mike Wilson', 'Math', 80);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10026, 'Amy Smith', 'Math', 95);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10027, 'Jack Lee', 'Math', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10028, 'Jill Johnson', 'Math', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10029, 'Sam Brown', 'Math', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10030, 'Janet Davis', 'Math', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10031, 'Bob Wilson', 'Physics', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10032, 'Mary Smith', 'Physics', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10033, 'Tom Lee', 'Physics', 90);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10034, 'Sue Johnson', 'Physics', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10035, 'Jim Brown', 'Physics', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10036, 'Carol Davis', 'Physics', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10037, 'David Wilson', 'Physics', 80);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10038, 'Linda Smith', 'Physics', 95);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10039, 'Mike Lee', 'Physics', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10040, 'Amy Johnson', 'Physics', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10041, 'Jack Brown', 'Physics', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10042, 'Jill Davis', 'Physics', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10043, 'Sam Wilson', 'Physics', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10044, 'Janet Smith', 'Physics', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10045, 'Bob Lee', 'Biology', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10046, 'Mary Johnson', 'Biology', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10047, 'Tom Brown', 'Biology', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10048, 'Sue Davis', 'Biology', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10049, 'Jim Wilson', 'Biology', 90);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10050, 'Carol Smith', 'Biology', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10051, 'David Miller', 'Biology', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10052, 'Linda Johnson', 'Biology', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10053, 'Mike Brown', 'Biology', 80);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10054, 'Amy Lee', 'Biology', 95);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10055, 'Jack Davis', 'Biology', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10056, 'Jill Wilson', 'Biology', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10057, 'Sam Smith', 'Biology', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10058, 'Janet Johnson', 'Biology', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10059, 'Bob Brown', 'Chemistry', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10060, 'Mary Davis', 'Chemistry', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10061, 'Tom Wilson', 'Chemistry', 90);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10062, 'Sue Smith', 'Chemistry', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10063, 'Jim Lee', 'Chemistry', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10064, 'Carol Johnson', 'Chemistry', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10065, 'David Brown', 'Chemistry', 80);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10066, 'Linda Davis', 'Chemistry', 95);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10067, 'Mike Wilson', 'Chemistry', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10068, 'Amy Smith', 'Chemistry', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10069, 'Jack Lee', 'Chemistry', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10070, 'Jill Johnson', 'Chemistry', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10071, 'Sam Brown', 'Geology', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10072, 'Janet Davis', 'Geology', 150);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10073, 'Bob Wilson', 'Geology', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10074, 'Mary Smith', 'Geology', 130);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10075, 'Tom Lee', 'Geology', 140);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10076, 'Sue Johnson', 'Geology', 100);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10077, 'Jim Brown', 'Geology', 90);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10078, 'Carol Davis', 'Geology', 110);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10079, 'David Wilson', 'Geology', 120);
INSERT INTO student (ID, name, dept_name, tot_cred) VALUES (10080, 'Linda Smith', 'Geology', 150);

注意：这些语句仅是示例，仅可用于学习和测试目的，不应用于实际生产环境。

为什么我使用上面这段代码后student表中的tot_cred变成了NULL??

可能是因为在执行触发器之前，student 表中的 tot_cred 字段已经被设置为 NULL。如果这是情况，那么在执行 UPDATE 语句时，tot_cred 字段的值将仍然是 NULL。为了解决这个问题，可以在 UPDATE 语句中使用 COALESCE 函数，将 NULL 值替换为 0。修改后的触发器代码如下所示：

create trigger tr_insert on takes for insert as 
declare @sno varchar(5) 
declare @cno varchar(5) 
declare @credit numeric(3,0) 

select @sno=ID,@cno=course_id from inserted  
select @credit=credits  from course  where course_id=@cno 

update student set tot_cred=COALESCE(tot_cred, 0) + @credit  
where ID=@sno 

go

这样，即使 tot_cred 字段的值为 NULL，触发器也会将其视为 0，并将 @credit 值加到 tot_cred 中。