比较同一张表中第 1 行的 A 列和第 2 行的 B 列

小编典典

比较同一张表中第 1 行的 A 列和第 2 行的 B 列

sql

我有一个表名“表 A”，我需要根据计数 > 1 的“AdviserBusinessId”和“ClientcontactGuid”两列获取所有值。我可以使用自联接来实现这一点，如下查询。

select gc.AdviserBusinessId,gc.ClientContactGuid,gc.PlanStartDate,gc.PlanEndDate,gc.ClientEngagementGuid, gc.RenewalGuid,
ROW_NUMBER() over(partition by gc.adviserbusinessid,gc.clientcontactguid order by gc.planenddate asc) as rownum from GENIUS_ClientEngagement gc
inner join(
select AdviserBusinessId,ClientContactGuid from GENIUS_ClientEngagement
group by AdviserBusinessId,ClientContactGuid having count(*) > 1) B
on gc.AdviserBusinessId = b.AdviserBusinessId and gc.ClientContactGuid = b.ClientContactGuid

这就是表格的样子：

现在我的主要观点是，我想将第 1 行的 PlanEndDate 与第 2 行的 PlanStartDate 进行比较，如果 PlanEndDate > PlanStartDate 则获取行。让我们以上面两行为例，如果假设 planstartdate 是 < planenddate 那么我只想填充上面两行。

光标或循环会对此有所帮助吗？

提前致谢。任何建议将不胜感激。

阅读 184

2022-07-21

共1个答案

小编典典

使用分析函数：

SELECT AdviserBusinessId,
       ClientContactGuid,
       PlanStartDate,
       PlanEndDate,
       ClientEngagementGuid,
       RenewalGuid,
       rn
FROM   (
  SELECT AdviserBusinessId,
         ClientContactGuid,
         PlanStartDate,
         PlanEndDate,
         ClientEngagementGuid,
         RenewalGuid,
         ROW_NUMBER() OVER (
           PARTITION BY adviserbusinessid, clientcontactguid
           ORDER BY planEndDate asc
         ) AS rn,
         COUNT(*) OVER (
           partition by adviserbusinessid, clientcontactguid
         ) AS num_rows,
         LEAD(planStartDate) OVER (
           PARTITION BY adviserbusinessid, clientcontactguid
           ORDER BY planEndDate asc
         ) AS next_start,
         LAG(planEndDate) OVER (
           PARTITION BY adviserbusinessid, clientcontactguid
           ORDER BY planEndDate asc
         ) AS prev_end
  FROM   GENIUS_ClientEngagement
) gce
WHERE num_rows > 1
AND   (  (rn = 1 AND planEndDate > next_start)
      OR (rn = 2 AND prev_end > planStartDate) )

2022-07-21