此篇收集一些mysql经常容易犯的小错误。
目录
1. 错误代码:Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate
2. 错误代码: 1248 Every derived table must have its own alias
3. 错误代码: 1111 Invalid use of group function
4. 错误代码:Operand should contain 1 column(s)
1. 错误代码:Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate
先看查询语句
SELECT salary
FROM employees
GROUP BY department_id
改正
SELECT AVG(salary )
FROM employees
GROUP BY department_id
在select中添加了聚合函数,这是由于在以部门号进行聚合过后,没有相应的聚合操作,即经过
GROUP BY department_id
聚合过后,答案没有进行处理,应该有相应的求最大值最小值平均值之类的操作。
2. 错误代码: 1248 Every derived table must have its own alias
先看查询语句
SELECT MIN(sal)
FROM(SELECT AVG(salary) sal
FROM employees
GROUP BY job_id )
错误提示已经恨明显了,每个得到的表都应该有它自己的名字,在From后加上表的别名就可以了
改正:
SELECT MIN(sal)
FROM(SELECT AVG(salary) sal
FROM employees
GROUP BY job_id ) avg_sal
3. 错误代码: 1111 Invalid use of group function
看代码
SELECT MIN(AVG(salary) )
FROM employees
GROUP BY job_id
mysql中不能聚合函数嵌套,可以嵌套一层,先用子查询实现AVG
改正:
SELECT MIN(sal)
FROM(SELECT AVG(salary) sal
FROM employees
GROUP BY job_id ) avg_sal
4. 错误代码:Operand should contain 1 column(s)
看代码
select university
from user_profile
where device_id in (select q.device_id,count(q.device_id)
from question_practice_detail q
group by q.device_id)
原因是in条件后面有多个字段,in后面只能有一个字段。
改正
select university
from user_profile
where device_id in (select q.device_id
from question_practice_detail q
group by q.device_id)