ORACLE SQL:使用嵌套条件添加数据
ORACLE SQL:使用嵌套条件添加数据
提问于 2018-06-01 03:06:56
我似乎不太明白如何只在数据存在的地方添加数据。
我有一个声明，我想要添加字段。但是，不是只在满足条件(即WHERE语句)的情况下才拉出员工，我希望当且仅当数据存在时才关联数据。我的基本语句提取30条记录，但当我向WHERE语句添加更多详细信息(以包括其他字段)时，它将记录计数降至20。我如何保留我的30条记录，同时还包括来自不同表的详细信息(如果它们存在)？
我的基本语句-它提取了30条记录
SELECT DISTINCT EMPLOYEE_NUM "Employee #",
START_DATE "Start Date",
NAME "Employee Name"
FROM EMPLOYEES E
JOIN EMPLOYEE_DETAILS D ON D.EMPLOYEE_ID = E.EMPLOYEE_ID
WHERE D.START_DATE >= DATE '2016-12-14'
ORDER BY 1;输出Ex.
Employee # | Start Date | Employee Name
1234 12/15/2017 Jim Doe
1456 01/16/2017 John Dillin
5435 04/23/2017 Jane Mitchel
9876 09/12/2017 Joan Smith
7655 10/14/2017 Barry Gibb
...25 more records包含额外字段的详细语句-它仅提取20条记录
SELECT DISTINCT EMPLOYEE_NUM "Employee #",
START_DATE "Start Date",
NAME "Employee Name",
OS.ONBOARDING_LOCATION "On-boarding Location",
OS.COMPLETION_DATE "Completion Date"
FROM EMPLOYEES E
JOIN EMPLOYEE_DETAILS D ON D.EMPLOYEE_ID = E.EMPLOYEE_ID
JOIN ONBOARDING_STATUS OS ON OS.EMPLOYEE_ID = E.EMPLOYEE_ID
WHERE D.START_DATE >= DATE '2016-12-14'
AND OS.DESCRIPTION LIKE 'START'
AND OS.CANCELLED IS NULL
ORDER BY 1;输出示例
Employee # | Start Date | Employee Name | On-boarding Location | Completion Date
1234 12/15/2017 Jim Doe Sacramento, CA 12/13/2017
1456 01/16/2017 John Dillin Atlanta, GA 01/19/2017
7655 10/14/2017 Barry Gibb Los Angeles, CA 10/17/2017
...17 more records下面是我尝试做的事情，但它只复制了记录：
SELECT DISTINCT EMPLOYEE_NUM "Employee #",
START_DATE "Start Date",
NAME "Employee Name",
(CASE
WHEN OS.DESCRIPTION LIKE 'START' AND OS.CANCELLED IS NULL
THEN OS.ONBOARDING_LOCATION
ELSE NULL
END)"On-boarding Location",
(CASE
WHEN OS.DESCRIPTION LIKE 'START' AND OS.CANCELLED IS NULL
THEN OS.COMPLETION_DATE
ELSE NULL
END)"Completion Date"
FROM EMPLOYEES E
JOIN EMPLOYEE_DETAILS D ON D.EMPLOYEE_ID = E.EMPLOYEE_ID
JOIN ONBOARDING_STATUS OS ON OS.EMPLOYEE_ID = E.EMPLOYEE_ID
WHERE D.START_DATE >= DATE '2016-12-14'
ORDER BY 1;我的最后一次尝试拉取了数据，但似乎不符合WHEN语句的情况，并且复制了许多记录。如果这不合理，请让我知道。如果您能提供任何帮助或提示，我们将不胜感激。
提前感谢！
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-01 03:13:27
使用外部联接，如下所示：
SELECT
DISTINCT EMPLOYEE_NUM "Employee #",
START_DATE "Start Date",
NAME "Employee Name",
OS.ONBOARDING_LOCATION "On-boarding Location",
OS.COMPLETION_DATE "Completion Date"
FROM EMPLOYEES E
left JOIN EMPLOYEE_DETAILS D ON D.EMPLOYEE_ID = E.EMPLOYEE_ID
and D.START_DATE >= DATE '2016-12-14'
left JOIN ONBOARDING_STATUS OS ON OS.EMPLOYEE_ID = E.EMPLOYEE_ID
AND OS.DESCRIPTION LIKE 'START'
AND OS.CANCELLED IS NULL
ORDER BY 1;请注意，我将过滤条件(WHERE部分)移到了join子句中，以强制执行外部连接。如果您将过滤器保留在WHERE子句中，那么您实际上是在将连接转换回内部连接，而您不希望这样做。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50631193
复制相关文章
相似问题
腾讯云开发者
