TSQL 如何管理查找或处理重复项

Question

>我有一个返回重复项的查询 - 我如何修改查询以防止这种情况或进行修复，以免发生这种情况。我尝试使用不同的，但无济于事。

这是查询

select sigfind.*
from [RM-JOB] 
inner join  (
select [JOB-NO], [LINK-JOB-NO] 
from [RM-LINK-JOBS] 
where [RM-LINK-JOBS].[reason-code] = 'FRA' 
) linkedjob on [RM-JOB].[JOB-NO] = linkedjob.[JOB-NO] 
inner join [RM-JOB] [RMLinkedJob] on linkedjob.[LINK-JOB-NO] = [RMLinkedJob].[JOB-NO] 
and [linkedjob].[JOB-NO] = [RM-JOB].[JOB-NO]
inner join (
SELECT ar.JobNo, ar.[ActionRef], AR.ActionStatus, AR.ActionDueDate [LINKED VISIT TARGET DATE], ar.SignificantFindings [SignificantFindings], ar.SyncDate, ar.dateFirRiskIdentified [Date of FRA] , ard.caseref [Linked Case Number]
FROM [CYHSQL01].[TM_FireRiskAssessment].[dbo].[ActionRequired] ar
inner join [CYHSQL01].[TM_FireRiskAssessment].[dbo].[ActionRequireddetails] ard
on AR.ResultID = ARD.ResultID and ard.actionref = ar.[ActionRef]
) sigfind on sigfind.JobNo = linkedjob.[JOB-NO]
where [RMLinkedJob].[JOB-TYPE] in ('FSRE') 
and [RM-JOB].[JOB-STATUS] in ('06','90')

这就是输出;

JobNo       ActionRef   ActionStatu LINKED VISIT                                                   TARGET DATE  SignificantFindings SyncDate    Date of FRA Linked Case Number
10265985    CP1 Closed  2019-01-18  Shoe cabinet is in the communal hallway door is a potential trip hazard when evacuating.    2019-10-18 11:11:41.457 2019-10-18  NULL
10265985    CP1 Closed  2019-01-18  Shoe cabinet is in the communal hallway door is a potential trip hazard when evacuating.    2019-10-18 11:11:41.457 2019-10-18  NULL
10265985    CP2 Closed  2019-10-25  Large screen tv has been fly tipped in the communal hallway.    2019-10-18 11:11:41.460 2019-10-18  NULL
10265985    CP2 Closed  2019-10-25  Large screen tv has been fly tipped in the communal hallway.    2019-10-18 11:11:41.460 2019-10-18  NULL
10265985    CP1 Closed  2019-01-18  Shoe cabinet is in the communal hallway door is a potential trip hazard when evacuating.    2019-10-18 11:11:41.457 2019-10-18  NULL
10265985    CP1 Closed  2019-01-18  Shoe cabinet is in the communal hallway door is a potential trip hazard when evacuating.    2019-10-18 11:11:41.457 2019-10-18  NULL
10265985    CP1 Closed  2019-01-18  Shoe cabinet is in the communal hallway door is a potential trip hazard when evacuating.    2019-10-18 11:11:41.457 2019-10-18  NULL
10265985    CP1 Closed  2019-01-18  Shoe cabinet is in the communal hallway door is a potential trip hazard when evacuating.    2019-10-18 11:11:41.457 2019-10-18  NULL
10265985    CP1 Closed  2019-01-18  Shoe cabinet is in the communal hallway door is a potential trip hazard when evacuating.    2019-10-18 11:11:41.457 2019-10-18  NULL
10265985    CP1 Closed  2019-01-18  Shoe cabinet is in the communal hallway door is a potential trip hazard when evacuating.    2019-10-18 11:11:41.457 2019-10-18  NULL
10265986    CP1 Closed  2019-10-28  There is a 5 litre petrol can stored under the stairs. This must be removed as a matter of urgency as it’s potentially highly flammable.    2019-10-21 13:05:56.533 2019-10-21  NULL
10265986    CP1 Closed  2019-10-28  There is a 5 litre petrol can stored under the stairs. This must be removed as a matter of urgency as it’s potentially highly flammable.    2019-10-21 13:05:56.533 2019-10-21  NULL
10265986    CP2 Open    2019-11-18  Shoe rack is being used in the communal area. This is a potential trip hazard in an evacuation situation.   2019-10-21 13:05:56.543 2019-10-21  NULL
10265986    CP2 Open    2019-11-18  Shoe rack is being used in the communal area. This is a potential trip hazard in an evacuation situation.   2019-10-21 13:05:56.543 2019-10-21  NULL
10265986    CP3 Open    2019-12-23  Christmas wreaths are a potential fire risk so must be removed. 2019-10-21 13:05:56.553 2019-10-21  Test Reference 12345
10265986    CP3 Open    2019-12-23  Christmas wreaths are a potential fire risk so must be removed. 2019-10-21 13:05:56.553 2019-10-21  Test Reference 12345
10265985    CP1 Closed  2019-01-18  Shoe cabinet is in the communal hallway door is a potential trip hazard when evacuating.    2019-10-18 11:11:41.457 2019-10-18  NULL
10265985    CP1 Closed  2019-01-18  Shoe cabinet is in the communal hallway door is a potential trip hazard when evacuating.    2019-10-18 11:11:41.457 2019-10-18  NULL

我可以使用窗口函数来删除重复项吗？我会使用 JobNo、ActionRef 和 Syncdate 来生成一个数字 1 并对其进行过滤？

提前感谢任何帮助。

Answer 1

您的重复可能是由连接条件引起的，尽管我不得不承认我在理解您的SQL代码方面遇到了麻烦。如果在select和sigfind.*之间添加 distinct 不起作用，请尝试将整个代码放入 WITH 子句中，然后选择 distinct。

with myResult as (
Your Query here
)
select distinct column1, column2, ..., columnX from myResult

最好通过您的查询逻辑，尝试修复它以使其更具可读性。