时间戳

首页 > 解决方案 > 根据另一个表过滤具有无效字段值的数据

sql - 根据另一个表过滤具有无效字段值的数据

问题描述

请考虑这个脚本：

declare @FirstTable   Table
(
    Name            nvarchar(50), -- Key
    Gender          tinyint,      -- Key   1 for Male, 2 for Female
    DateModified    datetime
)


insert into @FirstTable
Values(N'Tom' , 1, GETDATE()),
      (N'Tom' , 2, GETDATE()),
      (N'Clare' , 2, GETDATE()),
      (N'Lauren' , 1, GETDATE()),
      (N'Sara' , 2, GETDATE()),
      (N'Al' , 1, GETDATE()),
      (N'Kevin' , 1, GETDATE()),
      (N'Meril' , 2, GETDATE())


declare @Movies   Table
(
    Id              int identity(1,1),      
    Name            nvarchar(50),           -- Key
    ActorName       Nvarchar(50),      
    ActorGender     tinyint,
    ReleaseDate     datetime
)


insert into @Movies
Values (N'Movie 1', N'Tom' , 1, GETDATE()),
       (N'Movie 2', N'Kevin' , 2, GETDATE()),
       (N'Movie 3', N'Meril' , 2, GETDATE()),
       (N'Movie 4', N'Meril' , 2, GETDATE()),
       (N'Movie 5', N'Clare' , 1, GETDATE()),
       (N'Movie 6', N'Tom' , 2, GETDATE()),
       (N'Movie 7', N'Al' , 1, GETDATE()),
       (N'Movie 8', N'Sara' , 1, GETDATE()),
       (N'Movie 9', N'Ben' , 2, GETDATE()),
       (N'Movie 10', N'Viera' , 2, GETDATE()),
       (N'Movie 11', N'George' , 2, GETDATE()),
       (N'Movie 12', N'Frank' , 2, GETDATE()),
       (N'Movie 13', N'Kevin' , 2, GETDATE())

select  Id,     
        tmp.Name, 
        ActorName,      
        ActorGender,
        ReleaseDate
from @Movies tmp inner join @FirstTable gn on tmp.ActorName = gn.Name
where tmp.ActorGender <> gn.Gender

我想在Movie表格中选择性别无效的数据。例如，如果我们假设这Tom是男性和女性之间的通用名称，则上述查询会返回Tom结果（这是不正确的）。请帮助我更正我的选择语句。

编辑 1） 有一些记录（名称）只存在于Movies表中，并且没有对应的记录FirstTable。我不希望我的查询返回这些记录。

感谢@Nick，我想要这个结果：

Id  Name        ActorName   ActorGender     ReleaseDate
2   Movie 2     Kevin       2               22/12/2018 07:20:14
5   Movie 5     Clare       1               22/12/2018 07:20:14
8   Movie 8     Sara        1               22/12/2018 07:20:14
10  Movie 10    Kevin       2               22/12/2018 07:20:14

谢谢

标签： sqlsql-serversql-server-2014sql-server-2016

解决方案

更新

由于您不想获取表中存在名称但Movies表中不存在的记录，因此我们可以使用on和FirstTable过滤掉它们。然后我们可以像以前一样使用 来查找性别不匹配的演员：JOINMovies.ActorNameFirstTable.NameLEFT JOIN

Select  Id,     
        tmp.Name, 
        ActorName,      
        ActorGender,
        ReleaseDate
from Movies tmp 
join FirstTable gn on tmp.ActorName = gn.Name
left join FirstTable gn2 on tmp.ActorName = gn2.Name AND tmp.ActorGender = gn2.Gender
where gn2.Name IS NULL

更新的输出：

Id  Name        ActorName   ActorGender     ReleaseDate
2   Movie 2     Kevin       2               22/12/2018 07:20:14
5   Movie 5     Clare       1               22/12/2018 07:20:14
8   Movie 8     Sara        1               22/12/2018 07:20:14
13  Movie 13    Kevin       2               22/12/2018 07:20:14

更新了 dbfiddle 上的演示

原始答案

您可以使用 a在表中LEFT JOIN查找与Movies表中ActorGender的相应项不匹配的Gender条目FirstTable：

Select  Id,     
        tmp.Name, 
        ActorName,      
        ActorGender,
        ReleaseDate
from Movies tmp 
left join FirstTable gn on tmp.ActorName = gn.Name AND tmp.ActorGender = gn.Gender
where gn.Name IS NULL

输出：

Id  Name        ActorName   ActorGender     ReleaseDate
2   Movie 2     Kevin       2               22/12/2018 07:20:14
5   Movie 5     Clare       1               22/12/2018 07:20:14
8   Movie 8     Sara        1               22/12/2018 07:20:14
10  Movie 10    Kevin       2               22/12/2018 07:20:14

dbfiddle 上的演示

推荐阅读