select * from salary where year(date)=2018 and month(date)=12 and having basic>(select basic from salary where year(date)=2018 and month(date)=12 and employee_id=100); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'having basic>(select basic from salary where year(date)=2018 and month(date)=1' at line 1
时间: 2024-04-21 09:29:47 浏览: 45
您的 SQL 语法有误,HAVING 子句应该在 GROUP BY 子句之后使用,而您的 SQL 语句中没有 GROUP BY 子句,因此应该将 HAVING 子句改为 WHERE 子句,如下所示:
SELECT * FROM salary WHERE year(date)=2018 AND month(date)=12 AND basic>(SELECT basic FROM salary WHERE year(date)=2018 AND month(date)=12 AND employee_id=100);
相关问题
select dept_id,name,basic+bonus -> -> from salary inner join employees on salary.dept_id=employee.dept_id -> -> where dept_id=(select employee_id from employees where dept_id=(select dept_id from departments where dept_name='人事部') -> -> year(date)=2018 and month(date)=12; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'year(date)=2018 and month(date)=12' at line 7
在该SQL语句中,出现了语法错误。具体来说,是因为在WHERE子句中,使用了year()和month()函数来提取日期的年份和月份,但是这两个函数的使用方式不正确。正确的语法应该是将它们作为条件的一部分,如下所示:
```
select dept_id,name,basic+bonus
from salary
inner join employees on salary.dept_id=employee.dept_id
where dept_id=(
select employee_id from employees where dept_id=(
select dept_id from departments where dept_name='人事部'
)
) and year(date)=2018 and month(date)=12;
```
在这个修正后的SQL语句中,year()和month()函数被正确地用作WHERE子句的条件,而不是单独的函数调用。这个语句将会查询部门名称为“人事部”、时间为2018年12月份的员工的工资信息。
mysql> select * from employees as e union all salary as s on e.employee_id=s.employee_id where employee_id in (select employee_id from salary where year(date)=2018 group by employee_id having sum(basic+bonus)>300000);
这是一条 MySQL 查询语句,查询了两张表 employees 和 salary,将它们的结果合并,并筛选出在 salary 表中,基本工资和奖金总和超过 300000 的员工的信息。具体来说,查询了 employees 表中所有员工的信息,以及 salary 表中所有员工在 2018 年的薪资信息,并将它们合并起来。最后,根据条件筛选出符合要求的员工信息。