sql - JOIN on row by 连接查询中的行具有所有行的 MAX/MIN 值

Question

如果我想加入的查询返回:

IDApplication  ContactDate  CInfo
1              01/06/2016   pie
1              10/01/2016   cake
1              03/02/2015   banana
2              03/06/2016   cake
2              23/12/2015   apple



IDApplication  ReplyDate    RInfo
1              30/05/2016   circle
1              03/05/2016   square
1              04/02/2015   triangle
1              14/01/2016   pentagon
2              04/06/2016   square
2              01/02/2016   pentagon
2              10/06/2016   circle

我需要这个被退回:

IDApplication  ContactDate  CInfo       ReplyDate    RInfo    
1              01/06/2016   pie         NULL         NULL
1              10/01/2016   cake        30/05/2016   circle
1              03/02/2015   banana      04/02/2015   triangle
2              03/06/2016   cake        10/06/2016   square
2              23/12/2015   apple       01/02/2016   pentagon

我需要它返回第二个表/查询信息，其中它的日期大于第一个表中任何相应的申请日期，但不大于第一个表中的任何后续日期。

所以对于上面的第一条记录，它是 NULL 因为在回复表中没有日期在 01/06/2016 之后的回复信息(所以没有回复)，但是第二条记录的回复日期是2016 年 5 月 30 日，因为这是该申请的最长回复日期。更重要的是，对于第 5 条记录，回复日期是 01/02/2016，该日期大于联系日期，但不是申请 2 的最大回复日期，即 10/06/2016，但申请 2 的另一个联系日期是在这 2 个之间，它需要显示下一个联系日期之前的最大日期。

这是让我脑袋受伤的逻辑。

就回复日期大于联系日期的地方而言，我已经加入了第二个查询，但这会导致它使所有日期的行都显示得更大。

我需要加入日期大于基表日期的记录，但其中最大的日期不大于下一个最大的基表日期。

Answer 1

我解决此类问题的方法通常是将它们分解成小步骤，我可以将每个步骤作为 CTE 实现，这样我就能得到非常容易阅读和理解的内容。如果需要，我以后总是可以尝试将其折叠成更少的步骤。这是一个可能的解决方案。请参阅评论以了解其工作原理。

--------------------------------------------------------------------------------
-- Set up the sample data from the question.
--------------------------------------------------------------------------------
declare @Contact table (IDApplication int, ContactDate date, CInfo varchar(32));
declare @Reply table (IDApplication int, ReplyDate date, RInfo varchar(32));

insert @Contact values
    (1, '2016-06-01',' pie'),
    (1, '2016-01-10', 'cake'),
    (1, '2015-02-03', 'banana'),
    (2, '2016-06-03', 'cake'),
    (2, '2015-12-23', 'apple');
insert @Reply values
    (1, '2016-05-30', 'circle'),
    (1, '2016-05-03', 'square'),
    (1, '2015-02-04', 'triangle'),
    (1, '2016-01-14', 'pentagon'),
    (2, '2016-06-04', 'square'),
    (2, '2016-02-01', 'pentagon'),
    (2, '2016-06-10', 'circle');

--------------------------------------------------------------------------------
-- Step 1: Sequence each group of contacts by contact date.
--------------------------------------------------------------------------------
with OrderedContactCTE as
(
    select
        *,
        [Sequence] = row_number() over (partition by IDApplication order by ContactDate)
    from
        @Contact
),

--------------------------------------------------------------------------------
-- Step 2: Match each contact with the subsequent contact (where one exists)
--         having the same IDApplication value. The date of the subsequent
--         contact will act as the upper bound on reply dates that are valid for
--         the original contact. Assign each contact a unique identifier that
--         we'll use in the following step.
--------------------------------------------------------------------------------
PairedContactCTE as
(
    select
        UniqueID = row_number() over (order by Contact.IDApplication, Contact.[Sequence]),
        Contact.IDApplication,
        Contact.ContactDate,
        Contact.CInfo,
        NextContactDate = NextContact.ContactDate
    from
        OrderedContactCTE Contact
        left join OrderedContactCTE NextContact on
            Contact.IDApplication = NextContact.IDApplication and
            Contact.[Sequence] = NextContact.[Sequence] - 1
),

--------------------------------------------------------------------------------
-- Step 3: Match every contact with all replies that are strictly after the 
--         original contact date and, where applicable, strictly before the 
--         subsequent contact date. For each unique contact, sequence the 
--         replies in reverse order by reply date.
--------------------------------------------------------------------------------
OrderedResponseCTE as
(
    select
        Contact.*,
        Reply.ReplyDate,
        Reply.RInfo,
        [Sequence] = row_number() over (partition by Contact.UniqueID order by Reply.ReplyDate desc)
    from
        PairedContactCTE Contact
        left join @Reply Reply on
            Contact.IDApplication = Reply.IDApplication and
            Contact.ContactDate < Reply.ReplyDate and
            (
                Contact.NextContactDate is null or
                Contact.NextContactDate > Reply.ReplyDate
            )
)

--------------------------------------------------------------------------------
-- Step 4: Finally, select each contact and the date/info of the latest reply
--         which is an eligible match for that contact.
--------------------------------------------------------------------------------
select
    IDApplication,
    ContactDate,
    CInfo,
    ReplyDate,
    RInfo
from 
    OrderedResponseCTE 
where 
    [Sequence] = 1;