时间戳

首页 > 解决方案 > 基于两个表的重复条目

sql - 基于两个表的重复条目

问题描述

我有两个具有多列的表，我想找到表 1 中的一个名称在表 2 中具有相同父亲的位置。我试过这个：

SELECT
    d0.DateOfBirth,
    d.Id,d.Reg,
    d.Name, 
    D0.Id, 
    D0.FatherId,
    d1.Reg as Father_reg, 
    D1.Name as Fathers_Name, 
    D0.MotherId,
    d2.Reg as Mother_Reg, 
    D2.Name as Mothers_Name, 
FROM 
    dbo.Dogs d 
    LEFT JOIN dbo.Litters D0 ON D0.Id = d.LitterId
    LEFT JOIN dbo.Dogs D1 on D0.FatherId=D1.ID
    LEFT JOIN dbo.Dogs D2 on D0.MotherId=D2.ID
WHERE 
    d.Name IN (
        SELECT d.Name 
        FROM dbo.Dogs D 
        LEFT JOIN dbo.Litters D0 ON D0.Id = d.LitterId 
        GROUP BY d.Name  
        HAVING COUNT(*) > 1
    )
ORDER BY
    d.Name, 
    d0.DateOfBirth

这给了我所有的重复名称，但我想要所有具有相同父亲的重复名称。

因此，即使“Frank”在表中出现了四次，但其中只有两次他的父亲被命名为“Ian”，那么它应该只列出这两个条目。我遇到的问题是名称在表 dbo.dogs 中，而后代 ID 和父亲 ID 之间的连接在表 dbo.litters 中，所以当我尝试进行选择时，我在子查询中进行计数，并且只允许在那里进行一次选择. 英语不是我的母语，所以我希望它有意义；）

我做了一个小提琴来查看这里的数据。

我想看到的是：

DateOfBirth Id  Reg Name    Id  FatherId    Father_reg  Fathers_Name    MotherId    Mother_Reg  Mothers_Name
-------------------------------------------------------------------------------------------------------------
01/04/2012 00:00:00 3   NO34567/2012    Fido    9000    2   NO12345/2010    king    1   NO23456/2009    Queen    
01/04/2012 00:00:00 6   NO34567/2012    Fido    9000    2   NO12345/2010    king    1   NO23456/2009    Queen

小提琴中父亲名字相同的前两行，过滤掉父亲名字只有一次的地方。

小提琴中的解决方案：here

标签： sqlsql-serverduplicates

解决方案

这是您的示例数据：

SELECT * FROM dogs d LEFT JOIN litters l ON d.LitterId = l.id

身份证 | 注册 | 姓名 | 垃圾ID | 身份证 | 出生日期 | 父亲ID | 母亲身份
:- | :----------- | :----- | :------- | ---: | :----------------- | --------: | --------:
3 | NO34567/2012 | 菲多 | 9000 | 9000 | 2012 年 1 月 4 日 00:00:00 | 2 | 1
4 | NO34568/2012 | 菲多 | 6000 | 6000 | 2014 年 1 月 6 日 00:00:00 | 9 | 8
5 | NO34569/2012 | 菲多 | 5000 | 5000 | 01/05/2013 00:00:00 | 7 | 8
6 | NO34567/2012 | 菲多 | 9000 | 9000 | 2012 年 1 月 4 日 00:00:00 | 2 | 1
2 | NO12345/2010 | 国王 | 8000 | 空| 空                |     空|     空
1 | NO23456/2009 | 女王 | 7000 | 空| 空                |     空|     空
7 | NO12346/2010 | 上帝 | 8000 | 空| 空 |     空|     空
8 | NO23457/2009 | 女神 | 7000 | 空| 空                |     空|     空
9 | NO12346/2010 | 恶魔 | 8000 | 空| 无效的                |     空|     无效的

我了解您正在寻找具有相同父亲和相同名字的狗。在 SQL Server 中，一个简单的解决方案是使用窗口函数COUNT(...) OVER(...)来计算每条记录存在多少这样的重复项。

考虑：

SELECT * FROM (
    SELECT 
        d.ID, 
        d.Reg, 
        d.Name, 
        d.LitterID, 
        l.Dateofbirth, 
        l.FatherID, 
        l.MotherID, 
        COUNT(*) OVER(PARTITION BY d.Name, l.FatherId) cnt
    FROM dogs d 
    LEFT JOIN litters l ON d.LitterId = l.ID
) x WHERE cnt > 1

产量：

身份证 | 注册 | 姓名 | 垃圾ID | 出生日期 | 父亲ID | 妈妈ID | cnt
:- | :----------- | :--- | :------- | :----------------- | --------: | --------: | --:
3 | NO34567/2012 | 菲多 | 9000 | 2012 年 1 月 4 日 00:00:00 | 2 | 1 | 2
6 | NO34567/2012 | 菲多 | 9000 | 2012 年 1 月 4 日 00:00:00 | 2 | 1 | 2

现在剩下要做的就是一些额外的自连接来检索父母的名字：

SELECT
    x.DateOfBirth,
    x.ID,
    x.Reg,
    x.Name,
    x.FatherID,
    d_father.Reg FatherReg,
    d_father.Name FatherName,
    x.MotherID,
    d_mother.Reg MotherReg,
    d_mother.Name MotherName
FROM 
    (
        SELECT 
            d.ID, 
            d.Reg, 
            d.Name, 
            d.LitterID, 
            l.Dateofbirth, 
            l.FatherID, 
            l.MotherID, 
            COUNT(*) OVER(PARTITION BY d.Name, l.FatherId) cnt
        FROM dogs d 
        LEFT JOIN litters l ON d.LitterId = l.ID
    ) x 
    INNER JOIN dogs d_mother ON d_mother.ID = x.MotherID
    INNER JOIN dogs d_father ON d_father.ID = x.FatherID
WHERE x.cnt > 1

结果：

出生日期 | 身份证 | 注册 | 姓名 | 父亲ID | 父亲注册 | 父亲姓名 | 妈妈ID | 母亲注册 | 母亲姓名
:----------------- | :- | :----------- | :--- | --------: | :----------- | :--------- | --------: | :----------- | :---------
2012 年 1 月 4 日 00:00:00 | 3 | NO34567/2012 | 菲多 | 2 | NO12345/2010 | 国王 | 1 | NO23456/2009 | 女王     
2012 年 1 月 4 日 00:00:00 | 6 | NO34567/2012 | 菲多 | 2 | NO12345/2010 | 国王 | 1 | NO23456/2009 | 女王

DB Fiddle 上的演示

推荐阅读