首页 文章

从两个不在第三个表中的连接表中选择行

提问于
浏览
2

我有三张 table :

Table1: (EmployeeID int,EmployeeType int)

Table2: (AllowanceID int,EmployeeType int)

Table3: (EmployeeID int,AllowanceID int)

我需要一个查询来从两个表(Table1,Table2)中选择不在第三个表中的连接行(表3) .

I tried:

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
                 )

但没有成功 .

enter image description here

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)

5 回答

  • 0

    您的查询的问题是它希望 null 等于 nullwhich 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 |
    +------------+-------------+
    
  • 0
    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
    
  • 1

    您可以尝试使用带有所需表的子查询和带有您不想查找记录的表的左连接:

    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
    
  • 1

    如果您使用的是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
    
  • 1

    也许您可以尝试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)

相关问题