时间戳

首页 > 解决方案 > 两张表，返回不同的许可证号

sql - 两张表，返回不同的许可证号

问题描述

有两张表格，显示他们登记的狗的主人信息；一个用于 2018 年，另一个用于 2019 年。每个表具有相同的列、名称等。

我只需要按许可证号列出一条完整记录。比如狗可能不是2019年登记的，而是2018年登记的。所以如果2019表中不包含2018年的记录，我需要列出。如果有两条记录（一条为 2018 年，另一条为 2019 年），我只想列出 2019 年的记录。

联盟不起作用，即因为“RegNumber”或狗牌号码每年都在变化；更不用说可能的地址更改等。我尝试了 union、intersect 和 except 的组合，但似乎没有任何工作正常。有任何想法吗？

SELECT [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
FROM [dbo].[Dog_Info_2019]
WHERE Flag is not null

UNION

SELECT [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
FROM [dbo].[Dog_Info_2018] 
WHERE Flag is not null

ORDER BY [LicenseNumber]

当我执行上述查询时，显示 2018 年和 2019 年的记录，总共有 22569 条记录；11176 行在 2018 表中，11176 行在 2019 表中。

我什至尝试过 NOT IN，但这仅列出了不在一个表中的记录。

谢谢..

标签： sqlsql-serverselectuniondistinct

解决方案

我用我创建的一些模拟数据测试了下面的 SQL 脚本，它工作正常。我还创建了一个视图。我使用 UNION ALL 检索所有数据，然后使用窗口函数过滤外部 WHERE 中的重复项。我按 LicenseNumber 分区，因为它是动物独有的。

它不是最漂亮的，但它会得到你需要的数据，直到你想出一个更干净的解决方案。

SELECT LicenseYear
, [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS 
[Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], 
[Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog 
Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS 
[Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS 
[RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]

FROM  (
      SELECT ROW_NUMBER() OVER (PARTITION BY LicenseNumber ORDER BY LicenseNumber, LicenseYear DESC ) AS 'RowNum'
      , *
      FROM  (
            --SELECT 2020 AS 'LicenseYear'
            --, [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
            --FROM [dbo].[Dog_Info_2020]
            --WHERE Flag is not null

            --UNION ALL

            SELECT 2019 AS 'LicenseYear'
            , [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
            FROM [dbo].[Dog_Info_2019]
            WHERE Flag is not null

            UNION ALL

            SELECT 2018 AS 'LicenseYear'
            , [License Number] AS [LicenseNumber], [Last Name] AS [LastName], [First Name] AS [Firstname], [Street Name] AS [StreetName], [Address2], [City], [State], [Zip], [Phone], '' AS [Email], [Reg Number] AS [RegNumber], [Dog Name] AS [DogName], [Dog Breed] AS [Breed], [Purchased] AS [Year], [Sex], [Altered], [Color], [Vet] AS [Veterinarian], [RabieTag] AS [RabiesTagNumber], [RabiesTag_Exp] AS [RabiesTagExpiration], [Dog Number] AS [OwnerID], [Reg Number] AS [TagNumber]
            FROM [dbo].[Dog_Info_2018] 
            WHERE Flag is not null
            ) Data
     ) Data 
WHERE RowNum = 1

推荐阅读