sql - 为什么 SQL 查询返回重复项？

Question

我有以下查询。奇怪的是，它为同一个人返回多条记录 - 但它应该为每个人只返回一行。这都是基于 CONTACT1 C 的 LEFT JOINS - 每个人只有一行，不像其他列有时为同一个人有多行。

    select 
 C.ACCOUNTNO as 'AdmitGold Account',
 C2.UNAMEFIRST as 'First Name',
 C2.UNAMELAST as 'Last Name',
 C.KEY1 as 'Status',
 C.KEY4 as 'People ID',
 C.KEY3 as 'Type',
 C.KEY5 as 'Counselor',
 C.CITY as 'City',
 C.STATE as 'State',
 C.SOURCE as 'Source',
 C.DEPARTMENT as 'Major',
 C2.UGENDER as 'Gender',
 C2.UETHNICBG as 'Ethnicity',
 C2.UFULLPART as 'Full/Part',
 SLF_CLG_CS.EXT as 'College - GPA',
 OFF_CLG_CS.EXT as 'College - GPA Official',
 HS_OFF_CS.LINKACCT as 'HS GPA - Official',
 OFF_SAT_COMP.LINKACCT as 'SAT - Verbal',
 OFF_SAT_COMP.COUNTRY as 'SAT - Math',
 (Cast(OFF_SAT_COMP.LINKACCT as float) + Cast(OFF_SAT_COMP.COUNTRY as float)) as 'SAT - Composite',
 OFF_SAT_COMP.EXT as 'SAT - Essay',
 OFF_ACT_COMP.LINKACCT as 'ACT - English',
 OFF_ACT_COMP.COUNTRY as 'ACT - Math',
 OFF_ACT_COMP.ZIP as 'ACT - Reading',
 OFF_ACT_COMP.EXT as 'ACT - ScRe',
 (Cast(OFF_ACT_COMP.LINKACCT as float) + Cast(OFF_ACT_COMP.COUNTRY as float)+ Cast(OFF_ACT_COMP.ZIP as float) + Cast(OFF_ACT_COMP.EXT as float)) as 'ACT - Official' 
     from contact1 C
 left join CONTACT2 C2 on C.ACCOUNTNO=C2.ACCOUNTNO
 left join CONTSUPP HS_OFF_CS on C.ACCOUNTNO=HS_OFF_CS.ACCOUNTNO
  AND HS_OFF_CS.STATE='O' AND HS_OFF_CS.CONTACT='High School'
 left join CONTSUPP SLF_CLG_CS on C.ACCOUNTNO=SLF_CLG_CS.ACCOUNTNO
  AND SLF_CLG_CS.CONTACT = 'Transfer College' AND SLF_CLG_CS.STATE='S'
 left join CONTSUPP OFF_CLG_CS on C.ACCOUNTNO=OFF_CLG_CS.ACCOUNTNO
  AND OFF_CLG_CS.CONTACT = 'Transfer College' AND OFF_CLG_CS.STATE='O'
 left join CONTSUPP OFF_SAT_COMP on C.ACCOUNTNO=OFF_SAT_COMP.ACCOUNTNO
  AND OFF_SAT_COMP.CONTACT='Test/SAT' AND OFF_SAT_COMP.ZIP='O'
 left join CONTSUPP OFF_ACT_COMP on C.ACCOUNTNO=OFF_ACT_COMP.ACCOUNTNO
  AND OFF_ACT_COMP.CONTACT='Test/ACT' AND OFF_ACT_COMP.STATE='O'
     where 
 C.KEY1!='00PRSP' 
 AND C.U_KEY2='2010 FALL'

Answer 1

左连接将在一对多关系中产生重复项。无论您的第一个表中有多少条记录，如果您将第一个表中的每条记录的多行连接到一个表，您将获得不止一行。如果所有列的行实际上都是重复的，则 Select Distinct 将删除重复项，但不会消除在 中具有不同值的“重复项”。任何 柱子。