oracle SQL Subqueries (error in the subquery section)

jvlzgdj9 于 2022-12-11 发布在 Oracle

关注(0)|答案(2)|浏览(219)

问题是查找与Zlotkey在同一部门工作的雇员的姓氏和雇用日期。为了回答这个问题，我编写了以下代码。但是我得到一个错误（错误：ORA-01427：单行子查询返回多行）

SELECT last_name, hire_date, department_name
From   employees,
       departments
WHERE  department_name = (
         select department_name
         from   employees,
                departments
         where  last_name = 'Zlotkey'
       );

如果你能让我知道我的代码有什么问题，以及应该如何处理，我将不胜感激。

oracle

来源：https://stackoverflow.com/questions/74753950/sql-subqueries-error-in-the-subquery-section

2条答案

按热度按时间

up9lanfz1#

假设employees和departments表中有ID字段，请尝试：

SELECT 
    last_name, 
    hire_date, 
    department_name
FROM employees
JOIN departments
ON employees.employee_id = departments.employee_id
AND departments.department_id IN (
    SELECT DISTINCT department_id 
    FROM departments
    JOIN employees
    ON employees.employee_id = departments.employee_id
    AND last_name = 'Zlotkey');

这将处理一个或多个员工的姓氏为Zlotkey的情况。
如果有多个Zlotkey，并且您的条件需要特定的员工，则可以使用他们的特定ID：

SELECT
    last_name, 
    hire_date, 
    department_name
FROM employees
JOIN departments
ON employees.employee_id = departments.employee_id
AND departments.department_id IN (
 SELECT department_id
 FROM departments 
 WHERE employee_id = ... );

赞(0）回复(0）举报 2022-12-11

2wnc66cl2#

您可以JOIN这两个表（而不是使用CROSS JOIN），然后使用分析COUNT函数来计算每个部门中last_name为Zlotkey的人数，然后进行筛选，以便仅显示满足以下条件的部门的详细信息：

SELECT last_name,
       hire_date,
       department_name
FROM   (
  SELECT e.last_name,
         e.hire_date,
         d.department_name,
         COUNT(CASE e.last_name WHEN 'Zlotkey' THEN 1 END) OVER (
           PARTITION BY d.department_id
         ) AS has_zlotkey
  FROM   employees e
         INNER JOIN departments d
         ON (e.department_id = d.department_id)
)
WHERE  has_zlotkey > 0;

或者，您可以使用EXISTS：

SELECT e.last_name,
       e.hire_date,
       d.department_name
FROM   employees e
       INNER JOIN departments d
       ON (e.department_id = d.department_id)
WHERE  EXISTS (
         SELECT 1
         FROM   employees x
         WHERE  last_name = 'Zlotkey'
         AND    e.department_id = x.department_id
       )

或IN：

SELECT e.last_name,
       e.hire_date,
       d.department_name
FROM   employees e
       INNER JOIN departments d
       ON (e.department_id = d.department_id)
WHERE  e.department_id IN (
         SELECT department_id
         FROM   employees
         WHERE  last_name = 'Zlotkey'
       )

赞(0）回复(0）举报 2022-12-11

我来回答

oracle SQL Subqueries (error in the subquery section)

2条答案

相关问题

热门标签

最新问答