sql-server - 如何将重复记录汇总到查询结果中的特定记录

Question

在我的查询结果中，我在特定政策中为驾驶员获得了重复的车辆，并且在我的初始查询中结果很好。这是我的结果：

从上面的图片中，我想仅显示保单持有人的车辆记录，并从同一保单中的活动记录中删除这些相同的车辆。这是我希望它的样子：

这是我的sql代码：

WITH CTE1 as 
(
SELECT pr1.*
FROM #tempPIFRecords pr1
), 
CTE2 as (
SELECT pavr.*
FROM #tempPIFAssocVehicleRecords pavr
)

SELECT CTE1.*, CTE2.Year, CTE2.Make, CTE2.Model, CTE2.[Effective Date], CTE2.[Expiration Date]
FROM CTE1
LEFT OUTER JOIN CTE2
ON CTE1.AgentNo = CTE2.AgentNo
AND CTE1.PolicyNo = CTE2.PolicyNo
ORDER BY CTE1.AgentNo asc, CTE1.PolicyNo asc;

任何帮助/方向将不胜感激。谢谢你。

标签： sql-servertsqlrollup

Answer 1

这应该没问题：

WITH CTE1 as 
(
SELECT pr1.*
FROM #tempPIFRecords pr1
), 
CTE2 as (
SELECT pavr.*
FROM #tempPIFAssocVehicleRecords pavr
)

SELECT CTE1.*, ISNULL(CTE2.Year, '') AS Year, ISNULL(CTE2.Make, '') AS MAke, ISNULL(CTE2.Model, '') AS Model, ISNULL(CTE2.[Effective Date], '') AS [Effective Date], ISNULL(CTE2.[Expiration Date], '') AS [Expiration Date]
FROM CTE1
LEFT OUTER JOIN CTE2
ON CTE1.AgentNo = CTE2.AgentNo
AND CTE1.PolicyNo = CTE2.PolicyNo
AND CTE1.[DriverType] = 'PolicyHolder'
ORDER BY CTE1.AgentNo asc, CTE1.PolicyNo asc;