这是我需要进行查询的表的示例。
办公室表
---OFFICES----
OFFICE_ID - DEPARTMENT - EMPLOYEE_ID - MANAGER_ID
1 - ACCOUNTING - 1 - 1
2 - ACCOUNTING - 2 - 2
3 - IT - 3 - 3
员工表
---EMPLOYEES---
EMPLOYEE_ID - NAME - SURNAME
1 - JOHN - DOE
2 - JOHN2 - DOE2
3 - JOHN3 - DOE3
经理表
---MANAGERS---
MANAGER_ID - NAME - SURNAME
1 - JOHN - JONES
2 - GEORGE - GEORG
3 - ALEX - JON
我想根据 OFFICES 表进行查询，并根据员工和经理所在的部门对他们进行分组，但我不确定如何进行。例如 ：
---QUERY RESULTS---
ACCOUNTING - IT
JOHN DOE JOHN3 DOE
JOHN2 DOE ALEX JON
JOHN JONES
GEORGE GEOGE
请尝试以下...
SELECT CASE accountingRowsFinder.name
WHEN IS NULL THEN
''
ELSE
accountingRowsFinder.name
END || ' ' || CASE accountingRowsFinder.surname
WHEN IS NULL THEN
''
ELSE
accountingRowsFinder.surname
END AS Accounting,
CASE ITRowsFinder.name
WHEN IS NULL THEN
''
ELSE
ITRowsFinder.name
END || ' ' || CASE ITRowsFinder.surname
WHEN IS NULL THEN
''
ELSE
ITRowsFinder.surname
END AS IT
FROM ( SELECT rownum AS recordNumber,
name AS name,
surname AS surname,
COUNT( * ) AS accountingCount
FROM ( SELECT name,
surname
FROM Employees
JOIN Offices ON Employees.employee_id = Offices.employee_id
AND department = 'Accounting'
UNION
SELECT name,
surname
FROM Managers
JOIN Offices ON Managers.manager_id = Offices.manager_id
AND department = 'Accounting'
) accountingNamesFinder
) accountingRowsFinder
FULL JOIN ( SELECT rownum AS recordNumber,
name AS name,
surname AS surname,
COUNT( * ) AS ITCount
FROM ( SELECT name,
surname
FROM Employees
JOIN Offices ON Employees.employee_id = Offices.employee_id
AND department = 'IT'
UNION
SELECT name,
surname
FROM Managers
JOIN Offices ON Managers.manager_id = Offices.manager_id
AND department = 'IT'
) accountingNamesFinder
) ITRowsFinder ON accountingRowsFinder.recordNumber = ITRowsFinder.recordNumber
此语句为每个in选择name和surname值Employee，Accounting并将它们垂直连接到对应的Managersusing列表UNION。然后为来自该子查询的记录分配记录号（也称为行号）和来自子查询的所有记录的计数，然后返回到语句的主体。
然后采用相同的过程为department IT.
然后对从我们的子查询中获得的两个数据集执行A FULL JOIN（也称为 a FULL OUTER JOIN）accountingRowsFinder并ITRowsFinder基于它们的 值recordNumber。对于那些没有相应条目的行，较长的列表将为较短列表中的字段附加 NULL 值。
department然后连接每个连接数据集中的名称字段。甲CASE语句用来替换NULL有用于显示和级联目的的空字符串值
如果您有任何问题或意见，请随时发表相应的评论。
进一步阅读
http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm（上CASE）
https://docs.oracle.com/cd/B19306_01/server.102/b14200/operators003.htm（关于连接运算符||）
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm（关于连接）
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句