子查询实现
我有一个表员工
------------------------------------------------
| name | email | date_employment |
|-------+--------------------|-----------------|
| MAX | [email protected] | 2021-08-18 |
| ALEX | [email protected] | 1998-07-10 |
| ROBERT| [email protected] | 2016-08-23 |
| JOHN | [email protected] | 2001-03-09 |
------------------------------------------------
，我想编写一个子查询，该子查询将显示与他们在一起超过10年的员工。
SELECT employees.name, employees.email, employees.date_employment
FROM employees
WHERE 10 > (SELECT round((julianday('now') - julianday(employees.date_employment)) / 365, 0) FROM employees);
执行此请求后，它将显示所有员工，无论其资历如何。 如果您编写这样的请求，那么一切有效，
SELECT name, round((julianday('now') - julianday(employees.date_employment)) / 365, 0) as ex
FROM employees WHERE ex > 10;
为什么子查询无法正常工作？
I have a table employees
------------------------------------------------
| name | email | date_employment |
|-------+--------------------|-----------------|
| MAX | [email protected] | 2021-08-18 |
| ALEX | [email protected] | 1998-07-10 |
| ROBERT| [email protected] | 2016-08-23 |
| JOHN | [email protected] | 2001-03-09 |
------------------------------------------------
and I want to write a subquery that will display employees who have been with them for more than 10 years.
SELECT employees.name, employees.email, employees.date_employment
FROM employees
WHERE 10 > (SELECT round((julianday('now') - julianday(employees.date_employment)) / 365, 0) FROM employees);
After executing this request, it displays all employees, regardless of their seniority.
If you write a request like this, then everything works
SELECT name, round((julianday('now') - julianday(employees.date_employment)) / 365, 0) as ex
FROM employees WHERE ex > 10;
Why subquery is not working properly?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果执行子查询：
您会看到它返回4行：
在的条件下使用上面的结果集是没有意义的，其中条款将其与10进行比较。
但是sqlite允许它，而其他数据库会丢弃错误。
SQLite如何处理这种情况？
它仅选择结果集的1个值（通常是 first ），并在
中使用它子句，因此您的代码等同于：始终
truetrue < /code>，这就是为什么您将表的所有行作为结果。
If you execute the subquery:
you will see that it returns 4 rows:
It does not make sense to use the above resultset in a condition of a
WHEREclause to compare it with 10.But SQLite allows it, while other databases would throw an error.
How does SQLite handle this case?
It choses only 1 value of the resultset (usually the first) and uses it in the
WHEREclause, so your code is equivalent to:which is always
trueand this is why you get all the rows of the table as a result.