我有以下问题。奇怪的是,它返回同一个人的多个记录-但它应该只为每个人返回一行。它都是基于CONTACT1 C的左连接--每个人只有一行,不像其他列,有时同一个人有多行。
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'发布于 2010-07-03 03:57:36
左连接将在一对多关系中产生重复项。无论第一个表中有多少条记录,如果对第一个表中的每个记录都有多个行的表保留join,则会得到多个行。如果所有列的行实际上都是重复的,则Select Distinct将删除重复项,但不会消除在any列中具有不同值的“重复项”。
发布于 2010-07-05 01:38:31
如果您在服务器上显示了计划权限,则可以快速识别重复项的来源--添加一个where子句(例如WHERE C.ACCOUNTNO='some value'),您希望返回单行(但您已确定该值实际上返回了>1行),启用"Include Actual Execution Plan",运行查询并将鼠标悬停在计划各阶段之间的链接上--在某个时刻,您会发现有>1条记录来自特定阶段,查看此阶段的详细信息可以阐明复制的原因。
https://stackoverflow.com/questions/3168717
复制相似问题