比较子选择中不同行的值

Question

我试图收回一些数据，并在查询方面遇到困难。

我有两张桌子：

联系方式

• Contact_ID

注册

• Start_Date
• End_Date
• Owning_Contact_ID
• Product_ID
• Enrollment_ID

这是一对多的关系，每个联系人都有0到N个注册。我希望为每个联系人查找最新的两个注册日期(就开始日期而言)，并且只有当注册1的开始日期(最新的开始日期)和注册2的结束日期(第二至最新的开始日期)之间的差异大于1天或product_id在这两个注册期间之间的差异时，才返回它们。

我最初的想法是加入两个实例的注册表，但这是缓慢的，需要很长的时间。有更好的办法吗？

查询：

 select * from contact CONT with (nolock)
            INNER JOIN Enrollment ENROLL1 with (nolock)
            on (ENROLL1.Enrollment_ID =
            (SELECT TOP 1 EN.Enrollment_ID FROM Enrollment EN WITH(NOLOCK) 
                where EN.Contact_id = CONT.Contact_Id
                                     order by EN.start_date desc)
            INNER JOIN Enrollment ENROLL2 with (nolock)
            on (ENROLL2.Enrollment_ID =
            (SELECT TOP 1 EN2.Enrollment_ID FROM Enrollment EN2 WITH(NOLOCK) 
                where EN2.Contact_id = CONT.Contact_Id
                                     order by EN2.start_date desc)
                             where Enroll1.Product_ID != Enroll2.Product_ID

(我从未开始处理日期差异位，因为我试图在添加日期差异位之前先让上面的内容起作用)。

Answer 1

您可以通过以下操作找到最近的2个注册：

select e.*
from (select e.*, row_number() over (partition by contact_id order by start_date desc) as seqnum
      from enrollment e
     ) e
where seqnum <= 2;

您可以使用条件聚合和having子句获取所需的摘要信息：

select contact_id
from (select e.*, row_number() over (partition by contact_id order by start_date desc) as seqnum
      from enrollment e
     ) e
where seqnum <= 2
group by contact_id
having min(productid) <> max(productid) or
       max(start_date) > dateadd(day, 1, min(start_date))

如果您想要有关联系人的其他信息，可以返回到其他表。

要获得符合以下条件的最新两项注册：

select e.*
from (select e.*, row_number() over (partition by contact_id order by start_date desc) as seqnum
      from enrollment e
     ) e join
     (select contact_id
      from (select e.*, row_number() over (partition by contact_id order by start_date desc) as seqnum
            from enrollment e
           ) e
      where seqnum <= 2
      group by contact_id
      having min(productid) <> max(productid) or
             max(start_date) > dateadd(day, 1, min(start_date))
     ) ee
     on e.contact_id = ee.contact_id and e.seqnum <= 2;

Answer 2

像这样吗？

SELECT *
FROM Contact CONT
CROSS APPLY (SELECT TOP 1 Enrollment.* FROM Enrollment WHERE Owning_Contact_ID = CONT.Contact_ID ORDER BY Start_date desc)

哪些列有索引？注册需要Owning_Contact_ID和Start_date上的索引。