SQL - 链接信息

Question

我有一个数据库表，程序即将更新。 我在程序运行之前拍摄了表的快照，并在程序运行之后拍摄了快照。 下面是一个数据示例：

更新前：

JoinReference   User
1               User 1
1               User 2
1               User 3
2               User 4
2               User 5
2               User 6

更新后：

JoinReference   User
3               User 1
3               User 2
3               User 3
4               User 4
4               User 5
5               User 6

我正在尝试查找更新后未链接在一起的所有用户。 例如，用户 1;用户 2 和用户 3 在更新前后链接（即使它们具有不同的联接引用）。 更新后，用户 4 和 5 仍处于链接状态。 更新后未链接用户 6。 我正在尝试在更新后返回用户 6。 我尝试使用派生表，但没有奏效。

这适用于非常大的数据库。 我给出的示例使用我编造的字段来解释问题。 该示例的数据库结构如下所示：

创建表 加入用户 （连接引用 int， 用户）

Answer 1

select     UnpairedBefore.userid
from 
           (select    main.*
           from       tableBefore main
           left join  tableBefore ref
           on         main.joinreference = ref.joinreference
           and        main.userid <> ref.userid
           where      ref.userid null) UnpairedBefore
inner join
           (select    main.*
           from       tableAfter main
           left join  tableAfter ref
           on         main.joinreference = ref.joinreference
           and        main.userid <> ref.userid
           where      ref.userid null) UnpairedAfter
on         UnpairedBefore.userid = UnpairedAfter.userid

或

select  [user]
from    tblBefore
where   JoinReference in 
    (
    select  JoinReference
    from    tblBefore
    group by    JoinReference
    having  COUNT(*) = 1
    )
and [user] in 
    (
    select  [user]
    from    tblAfter
    where   JoinReference in 
        (
        select  JoinReference
        from    tblAfter
        group by    JoinReference
        having  COUNT(*) = 1
        )
    )

Answer 2

你想要类似的东西

SELECT *
  FROM myTable
 WHERE (row_was_updated)
   AND JoinReference IN
       (SELECT JoinReference
          FROM myTable
         GROUP BY JoinReference
        HAVING COUNT(*) = 1)

您可能需要也可能不需要WHERE (row_was_updated)如果需要，您必须自己填写该逻辑。

可能会有更有效的方法来实现这一点，但没有架构，只能提供一般指导。

Answer 3

SELECT b1.user AS user1
     , b2.user AS user2
FROM BeforeTable AS b1                          --- user1 and
  JOIN BeforeTable AS b2                        --- user2 were linked
    ON  b1.JoinReference = b2.JoinReference     --- before the update
WHERE b1.user < b2.user
  AND NOT EXISTS                                --- and are not linked
      ( SELECT *
        FROM AfterTable AS a1                   --- after the update
          JOIN AfterTable AS a2
            ON  a1.JoinReference = a2.JoinReference 
        WHERE a1.user = b1.user
          AND a2.user = b2.user
      ) ;