我如何多次加入这张桌子？

Question

我有一个表，其中列出了以下列：

雇员ssn，受抚养ssn，姓氏，姓名，关系，福利金额

在“关系”列中，它将显示员工、子女或配偶。

我需要的是：

雇员个人福利，姓氏，姓名，雇主福利，子女福利，配偶福利。。

我需要加入员工ssn匹配的福利金额列，但问题是，每当我这样做时，它会给出同一员工的倍数，有时子女和/或配偶福利是不正确的。

以下是我所写的：

SELECT a.[employee ssn], a.[last name], a.[first name], a.[benefit amount], b.[benefit amount] AS "child benefit", c.[benefit amount] AS "spouse benefit"
FROM allenrollments a
JOIN allenrollments b ON b.[employee ssn] = a.[employee ssn]
JOIN allenrollments c ON c.[employee ssn] = a.[employee ssn]
WHERE a.relationship = "Employee" AND b.relationship = "Child" AND c.relationship = "Spouse"

Answer 1

你得到了一个常见的交叉连接或笛卡尔积的问题。对于连接到第二个表的第一个表中的每个记录，也与第三个表(在本例中是基于公共雇员SSN )组合。

注意，由于许多原因，许多系统试图取消SSN作为主键，并且通常具有自动增量号，并且只使用SSN作为查找详细信息的基础。否则，您的整个系统都会被SSN淹没，如果被黑客攻击，那么暴露也不是好事(尽管被恶意/坏角色攻击时，被黑客攻击从来不是件好事)。

现在，这句话和你似乎拥有的东西。你试图获取配偶和孩子的数据。但是如果一个家庭保险福利计划有5个孩子呢？你的最终目标是什么？您可能更好的做法是获取所有行，并有一个列来表示它与谁相关联。然后，一旦你得到整个家庭计划的数据，你就可以做任何计数/和覆盖检查。

同时，列名中有空格也不是件好事。您将一直在追逐不平衡的括号来应对，将输出结果查询到类结构，例如获取事物列表等。

SELECT 
      emp.[employee ssn] SSN, 
      emp.[last name] EmpLastName, 
      emp.[first name] EmpFirstName, 
      emp.[benefit amount] EmpBenefitAmount,     
      fam.[last name] FamilyMemberLastName, 
      fam.[first name] FamilyMemberFirstName, 
      fam.relationship FamilyRelationship,
      case when fam.relationship = 'Spouse'
           then fam.[benefit_amount]
           else 0 end SpouseBenefit,
      case when fam.relationship = 'Spouse'
           then 0
           else fam.[benefit_amount] end ChildBenefit
   FROM 
      allenrollments emp
         JOIN allenrollments fam
            on emp.[employee ssn] = fam.[employee ssn]
           AND NOT emp.relationship = 'Employee'
   where
      emp.relationship = 'Employee'

因此，第一个表完全是针对被覆盖的员工的。然后由SSN返回到同一个表以查找所有其他家庭成员。我认为您甚至不需要进入SpouseBenefit与ChildBenefit列的级别。这可能只是作为FamilyMemberBenefitAmount的受益金额。

Answer 2

我假设存在一些潜在的数据问题，这就是为什么我们要自己加入，在这种情况下：

WITH dependants as (
SELECT 
 [employee ssn],
 [benefit amount],
 RANK() 
  OVER (
   PARTITION BY [employee ssn] 
   ORDER BY [benefit amount] DESC
  ) as unique_key
FROM allenrollments
WHERE 
 [relationship] IN('Child', 'Spouse')
)

SELECT 
 emp.[employee ssn],
 emp.[first name],
 emp.[last name],
 emp.[benefit amount] as EmployeeBenefit,
 dep.[benefit amount] as DependantBenefit
FROM allenrollments emp
JOIN 
 dependants dep on emp.[employee ssn] = dep.[employee ssn]
WHERE 
 dep.unique_key = 1 AND 
 emp.relationship = 'Employee'

在这里，我们使用一个CTE来创建一个查询，该查询只列出所有依赖的福利及其相关的员工ssns。我们使用一个职级()函数来提供我们可以过滤的东西(因为如果有多个受抚养人，我们将获得每个员工ssn的多条记录)。然后，我们在外部查询中筛选值'Employee‘和第一等级。