主页 / coding / 问题 / 77994483
Accepted
user1009073
Asked: 2024-02-14 20:58:48 +0800 CST

如何在 Oracle SQL 中执行多对多匹配（在同一个表中）

  • 772

我有一个包含员工信息的表。该表显示了整个员工层次结构。给定的员工可以处于层次结构的任何级别。如果我想知道特定员工的给定属性，我可以使用 where 子句编写 SQL 语句

where '[email protected]' in ANY(manager_3, manager_4, manager_5, manager_6, employee_email)

这很好用。挑战是当我有多名员工（可能是也可能不是经理）时。我无法更改表结构。我的查询最终结果是列出 1 名或多名员工的层次结构链中的所有个人。假设我有3个人。我不知道他们处于哪个层级。我需要找到层次结构中向这 3 个人汇报的每个人。我需要做的是与此类似的事情（并且我知道这不是有效的 SQL）...

select employee_email from myTable
where ('[email protected]', '[email protected]', '[email protected]') = 
     Any(manager_3, manager_4, manager_5, manager_6, employee_email)

有没有一种更干净/更优化的方法来编写这个而不是使用 3 个单独的 ANY 子句？

**** 编辑 ***** 只是为了以后搜索这个问题的人，我最终使用了答案中列出的 SUB_QUERY，但我没有使用 DUAL 进行多个查询，而是使用了分割例程...

WITH
    Q_STR AS (SELECT 'YOUR,COMMA,SEPARATED,STRING' STR FROM DUAL)
SELECT
    TRIM(COLUMN_VALUE) COL1
FROM Q_STR, 
XMLTABLE(('"' || REPLACE(STR, ',', '","') || '"'));
sql

1 个回答

  1. Best Answer

    使用OR：

    SELECT employee_email
FROM   myTable
WHERE  manager_3 IN ('[email protected]', '[email protected]', '[email protected]')
OR     manager_4 IN ('[email protected]', '[email protected]', '[email protected]')
OR     manager_5 IN ('[email protected]', '[email protected]', '[email protected]')
OR     manager_6 IN ('[email protected]', '[email protected]', '[email protected]')
OR     employee_email IN ('[email protected]', '[email protected]', '[email protected]')

    或者：

    SELECT employee_email
FROM   myTable
WHERE  '[email protected]' IN (manager_3, manager_4, manager_5, manager_6, employee_email)
OR     '[email protected]' IN (manager_3, manager_4, manager_5, manager_6, employee_email)
OR     '[email protected]' IN (manager_3, manager_4, manager_5, manager_6, employee_email)

    或连接到包含所需值的子查询分解子句：

    WITH lookup_emails (email) AS (
  SELECT '[email protected]' FROM DUAL UNION ALL
  SELECT '[email protected]' FROM DUAL UNION ALL
  SELECT '[email protected]' FROM DUAL
)
SELECT t.employee_email
FROM   myTable t
       INNER JOIN lookup_emails l
       ON l.email IN (t.manager_3, t.manager_4, t.manager_5, t.manager_6)

    或结合前两个使用EXISTS（而不是IN）：

    WITH lookup_emails (email) AS (
  SELECT '[email protected]' FROM DUAL UNION ALL
  SELECT '[email protected]' FROM DUAL UNION ALL
  SELECT '[email protected]' FROM DUAL
)
SELECT employee_email
FROM   myTable t
WHERE  EXISTS(
         SELECT 1
         FROM   lookup_emails l
         WHERE  l.email IN (t.manager_3, t.manager_4, t.manager_5, t.manager_6)
       )
    • 1

相关问题