从两个选择行连接表不是在第三个表
我有三个表:从两个选择行连接表不是在第三个表
表1: (雇员INT,EmployeeType INT)
表2: (AllowanceID INT,EmployeeType INT)
表3: (雇员INT,AllowanceID INT)
我需要一个查询来从两个表(Table1,Table2)中选择不在第三个表(Table3)中的连接行。
我想:
Select t1.EmployeeID, t2.AllowanceID
From Table2 t2 Inner Join
Table1 t1
on t1.EmployeeType = t2.EmployeeType
where Not Exists (select 1
From Table3 t3
where t3.EmployeeID = t1.EmployeeID and
t3.AllowanceID = t2.AllowanceID
)
,但没有成功。
CREATE TABLE [dbo].[Table1](
[EmployeeID] [int] NULL,
[EmployeeType] [tinyint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table2](
[AllowanceID] [int] NOT NULL,
[EmployeeType] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table3](
[AllowanceID] [int] NULL,
[EmployeeID] [int] NULL
) ON [PRIMARY]
Insert into Table1 (EmployeeID,EmployeeType)
Values
(352395, 10),
(352396, 16),
(352397, 15),
(352398, 10),
(3523991, 16),
(NULL, 16)
Insert into Table2 (AllowanceID, EmployeeType)
Values
(100, 50),
(30, 9),
(32, 10),
(37, 16),
(512, 28),
(6000, 10)
Insert into Table3 (AllowanceID,EmployeeID)
Values
(NULL, 352400),
(32, NULL),
(37, NULL),
(37, 352395),
(6000, 352395),
(30, 352396),
(32, 352396),
(37, 352396),
(512, 352396),
(6000, 352396),
(30, 352397),
(32, 352397),
(37, 352397),
(512, 352397),
(6000, 352397),
(30, 352398),
(32, 352398),
(37, 352398),
(512, 352398),
(6000, 352398),
(30, 352399),
(32, 352399),
(37, 352399),
(512, 352399),
(6000, 352399)
与您查询的问题是,它预计null
等于null
,which is not how null
works。
要解决该问题,只需将t3.EmployeeID = t1.EmployeeID
更改为(t3.EmployeeID = t1.EmployeeID or t1.EmployeeId is null)
即可。
查询变为:
select
t1.EmployeeID
, t2.AllowanceID
from Table2 t2
inner join Table1 t1
on t1.EmployeeType = t2.EmployeeType
where not exists (
select 1
from Table3 t3
where (t3.EmployeeID = t1.EmployeeID or t1.EmployeeId is null)
and t3.AllowanceID = t2.AllowanceID
)
rextester演示:http://rextester.com/OPRHN52820
+------------+-------------+
| EmployeeID | AllowanceID |
+------------+-------------+
| 352395 | 32 |
| 3523991 | 37 |
+------------+-------------+
完美..谢谢 –
如果您使用的是SQL SERVER然后用下面的查询
SELECT t1.empoyeeId, t2.AllowanceId
FROM table1 t1
INNER JOIN table2 t2 ON t2.EmployeeTypeId = t1.EmployeeTypeID
EXCEPT
SELECT employeeID , allowanceID
FROM table2
SELECT t1.EmployeeID, t2.AllowanceID
FROM Table1 t1
JOIN Table2 t2 ON t1.EmployeeType = t2.EmployeeType
LEFT JOIN Table3 t3 ON t3.AllowanceID = t2.AllowanceID AND t3.EmployeeID =
t1.EmployeeID
WHERE t3.EmployeeID IS NULL
请添加一些文字来解释您的答案 – EBH
或许你可以尝试EXCEPT,使用序列改变的结果
SELECT t1.EmployeeID , t2.AllowanceID
FROM Table2 t2
INNER JOIN Table1 t1 ON t1.EmployeeType = t2.EmployeeType
EXCEPT
SELECT t3.EmployeeID , t3.AllowanceID
FROM Table3 t3
;
SELECT t3.EmployeeID , t3.AllowanceID
FROM Table3 t3
EXCEPT
SELECT t1.EmployeeID , t2.AllowanceID
FROM Table2 t2
INNER JOIN Table1 t1 ON t1.EmployeeType = t2.EmployeeType
返回查询中EXCEPT 运算符左侧的所有不同的值,这些值也不是从正确的查询中返回的。 (ms docs)
您可以尝试使用子查询与所需的表格和一个左移,你想不来查找记录的表连接:
Select A.EmployeeID, A.AllowanceID
FROM (SELECT t1.EmployeeID, t2.AllowanceID
From Table2 t2 Inner Join
Table1 t1
on t1.EmployeeType = t2.EmployeeType) A
LEFT JOIN t3 ON t3.EmployeeID = A.EmployeeID and
t3.AllowanceID = A.AllowanceID
WHERE t3.EmployeeID IS NULL and t3.AllowanceID IS NULL
什么是 “没有成功的意思”?你能提供样本数据和期望的结果吗? –
我是第二个戈登·林诺夫的请求。作为一个说明,我编辑了你的帖子,在你的代码中添加了一些换行符(没有改变代码本身的任何东西)。事实上,它延伸得很远,用户不得不滚动浏览所有内容。只是一些反馈,为未来的职位格式。 – SandPiper
您发布的代码对我来说似乎很好,但没有示例数据(如DDL + DML),明确的问题描述和期望的结果很难回答您的问题。 –