sql - 根据成员(member)资格、成员(member)级别以及到期日期或成员(member)资格对用户进行重复数据删除

Question

我们有一个包含重复用户记录的数据库，我需要根据几个因素选择“最佳”用户:

1. 应先选择具有成员(member)资格的用户，然后再选择没有成员(member)资格的用户
2. 成员(member)有级别，在所有条件相同的情况下，应选择具有“最佳”成员(member)级别的用户。
3. 应先选择具有有效成员(member)资格的用户，然后再选择具有过期成员(member)资格的用户。

根据这些条件，我想出了类似以下查询的内容(实际查询太敏感)。

SELECT TOP 1 u.[UserId]
    FROM [dbo].[Users] u
    LEFT OUTER JOIN [dbo].[UserMemberships] um
        ON u.[UserId] = um.[UserId]
    LEFT OUTER JOIN [dbo].[Memberships] m
        ON um.[MembershipId] = m.[MembershipId]
    WHERE u.[Email] = @Email
    ORDER BY m.[Order] ASC, um.[Expires] DESC, u.[Created] DESC

我遇到的问题是成员(member)资格的排序与过期。例如，如果有两个具有不同级别的成员(member)资格的重复用户，我通常会希望具有“最佳”成员(member)资格级别的用户(基于顺序)，但如果该成员(member)资格已过期，我会想要未过期的用户，即使是较低的水平。但是，我不能简单地排除过期的成员(member)资格，因为我需要选择具有成员(member)资格的用户(即使已过期)，而不是没有成员(member)资格的用户。

本质上，按成员(member)资格顺序然后到期日期排序涵盖了大多数场景，但在这种特殊情况下，顺序应该是到期日期然后成员(member)资格顺序。我可以对查询进行哪些修改来涵盖这种边缘情况？

Answer 1

像这样的事情怎么办？通过利用一些 Case 语句来创建数字排名列，您仍然可以在查询中保留过期记录，但为它们分配较低的值，以便必须首先评估事件成员资格:

SELECT TOP 1 u.[UserId], 
                Case um.[Expires]
                    When null then 9999      --Inactive Membership
                    When >= GetDate() then 1 --Expired Membership
                    Else 0                   --Active Membership
                End as ActiveRank, 
                Case m.[Order]
                    When null then 9999      --No Membership
                    Else Order               --Membership Ranking
                End as MembershipRank
  FROM [dbo].[Users] u
  LEFT OUTER JOIN [dbo].[UserMemberships] um
    ON u.[UserId] = um.[UserId]
  LEFT OUTER JOIN [dbo].[Memberships] m
    ON um.[MembershipId] = m.[MembershipId]
  WHERE u.[Email] = @Email             
  ORDER BY ActiveRank ASC, MembershipRank ASC, u.[Created] DESC

注意:我不太确定你用 Created 做什么？字段，所以我将其留在订单中。

这意味着以下内容成立:

1. ActiveRank将把排序分为两个不同的组...列表的上半部分将按活跃成员(member)资格排序，下半部分将按过期或不存在的成员(member)资格排序。
2. MembershipRank将根据排名确定优先级(将空值或不存在的成员资格推到列表的过期成员资格部分的最底部。