SQL查询如何将同一客户的多项残疾信息合并至单条记录避免重复

你遇到的重复问题是直接关联残疾维度表disabilitiescrosstab导致的，单客户多残疾时会按残疾数拆分多行，通过预聚合残疾数据+分组聚合的方式即可实现单客户单案件仅返回一行记录，所有残疾标记合并到同一行。

修改逻辑说明 #

• 对每个残疾类型的判断逻辑套一层MAX()聚合函数，确保只要客户存在对应残疾就返回YES，无则返回NO
• 将所有非聚合的业务字段加入GROUP BY子句，按案件、客户维度聚合数据
• 也可提前将disabilitiescrosstab表按EntityID预聚合后再关联，查询性能更优

完整修改后查询语句 #

USE MyPersonalSupport_reporting

SELECT 
SC.Name AS 'Sub-Contract',
CSCH.Received AS LiveDate,
CS.ServiceEndDate AS ServiceEndDate,
CS.CaseReference,
CONTACT.FirstName AS 'Forename',
CONTACT.LastName AS 'Surname',
CONTACT.DateOfBirth AS DOB,
CONTACT.DateOfDeath,
CONTACT.Age,
CCAV.ConcatenatedAddress AS 'Full Address',
LK1.Value AS Ethnicity,
LK2.Value AS Sex,
LK3.Value AS Religion,
LK4.Value AS Sexuality,
LK5.Value AS Transgender,
LK6.Value AS Nationality,
LK7.Value AS 'First Language',
SO.Name AS ServiceOffering,
LK.Value AS CaseStatus,
DATEDIFF(day, CSCH.Received, CS.serviceenddate) AS 'Days Occupied',
CONCAT (EMP.FirstName, ' ' , EMP.LastName) AS KeyWorker, 
CASE WHEN CONTACT.HasDisibility = 1 THEN 'YES' ELSE 'NO' END AS HasDisability,
MAX(CASE WHEN DV.value = 'Autistic Spectrum Condition' THEN 'YES' ELSE 'NO' END) AS AutisticSpectrumCondition,
MAX(CASE WHEN DV.value = 'Hearing Impairment' THEN 'YES' ELSE 'NO' END) AS 'Hearing Impairment',
MAX(CASE WHEN DV.value = 'Learning Disability' THEN 'YES' ELSE 'NO' END) AS 'Learning Disability',
MAX(CASE WHEN DV.value = 'Mental Health' THEN 'YES' ELSE 'NO' END) AS 'Mental Health',
MAX(CASE WHEN DV.value = 'Mobility Disability' THEN 'YES' ELSE 'NO' END) AS 'Mobility Disability',
MAX(CASE WHEN DV.value = 'Progressive Disability / Chronic Illness' THEN 'YES' ELSE 'NO' END) AS 'Progressive Disability / Chronic Illness',
MAX(CASE WHEN DV.value = 'Visual Impairment' THEN 'YES' ELSE 'NO' END) AS 'Visual Impairment',
MAX(CASE WHEN DV.value = 'Other' THEN 'YES' ELSE 'NO' END) AS 'Other Disability',
MAX(CASE WHEN DV.value = 'Does not wish to disclose' THEN 'YES' ELSE 'NO' END) AS 'Does not wish to disclose'

FROM [MyPersonalSupport_reporting].[Mps].[Cases] AS CS
INNER JOIN mps.CaseContracts AS CC ON CS.caseid = CC.caseid 
INNER JOIN mps.CaseStatusChangeHistories AS CSCH ON CS.CaseId = CSCH.CaseId
INNER JOIN mps.Contacts AS CONTACT ON CS.CustomerId = CONTACT.ContactId
FULL OUTER JOIN mps.ContactCurrentAddress AS CCAV ON CONTACT.ContactID = CCAV.ContactId
FULL OUTER JOIN mps.LookupItems AS LK ON CSCH.StatusId = LK.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK1 ON CONTACT.EthnicityId = LK1.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK2 ON CONTACT.SexId = LK2.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK3 ON CONTACT.ReligionId = LK3.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK4 ON CONTACT.SexualityId = LK4.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK5 ON CONTACT.TransgenderId = LK5.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK6 ON CONTACT.NationalityId = LK6.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK7 ON CONTACT.FirstLanguageId = LK7.LookupItemId
FULL OUTER JOIN mps.SubContracts AS SC ON CC.SubContractId = SC.SubContractId
FULL OUTER JOIN mps.ServiceOfferings AS SO ON SC.ServiceOfferingId = SO.ServiceOfferingId
FULL OUTER JOIN mps.Employees AS EMP ON EMP.EmployeeId = CS.KeyWorkerId
FULL OUTER JOIN dbo.disabilitiescrosstab AS DV ON CONTACT.ContactId = DV.EntityID 

WHERE 
CSCH.Received >= '2000-01-01' AND CS.ServiceEndDate <= GETDATE()
AND CSCH.StatusId = 1392
AND CSCH.Archived = 0
AND CONTACT.Archived = 0
GROUP BY 
SC.Name,
CSCH.Received,
CS.ServiceEndDate,
CS.CaseReference,
CONTACT.FirstName,
CONTACT.LastName,
CONTACT.DateOfBirth,
CONTACT.DateOfDeath,
CONTACT.Age,
CCAV.ConcatenatedAddress,
LK1.Value,
LK2.Value,
LK3.Value,
LK4.Value,
LK5.Value,
LK6.Value,
LK7.Value,
SO.Name,
LK.Value,
DATEDIFF(day, CSCH.Received, CS.serviceenddate),
CONCAT (EMP.FirstName, ' ' , EMP.LastName),
CASE WHEN CONTACT.HasDisibility = 1 THEN 'YES' ELSE 'NO' END
ORDER BY CS.CaseId

可选优化方案 #

如果你的数据库支持STRING_AGG（SQL Server 2017及以上版本），还可以新增一个字段直接拼接所有残疾类型，方便快速查看：

STRING_AGG(DV.value, '、') WITHIN GROUP (ORDER BY DV.value) AS 所有残疾类型

直接加在SELECT字段列表里即可，不需要修改其他逻辑。

内容的提问来源于stack exchange，提问作者nocoyote5374