sql - 查找每个客户的第二个约会日期

Question

这是一个措辞糟糕的标题，但我想不出更好的，抱歉!

我们有一个看起来像这样的表格(为简洁起见进行了修剪):

create table Appointment (
    AppointmentId       int     not null identity(1, 1),
    CustomerId          int     not null,
    AppointmentDate     date    not null,

    constraint PK_Appointment primary key (AppointmentId),
    constraint FK_Appointment_Customer foreign key (CustomerId) references Customer(CustomerId)
)

我们正在尝试编写一个查询，以查找在给定日期范围内进行了 SECOND 约会的所有客户的详细信息。请注意，客户可能在同一天有两次约会。

我们可以使用一些 CTE 来做到这一点，但我确信有更好的方法(可能使用某种 row_number 类型的函数？)。有什么建议么？我真正不喜欢我们的解决方案的一点是它完全不灵活(当他们想在给定日期范围内查看THIRD 约会时会发生什么，等等)。

无论如何；这是我们想出的:

declare @startDate date = '2011-12-01'
declare @endDate date = '2011-12-31'
;
-- Limit to appointments before the end date
with AppointmentDates as (
    select
        AppointmentId,
        CustomerId,
        AppointmentDate
    from
        Appointment
    where
        AppointmentDate < @endDate
),

-- Get first appointment date - careful to cater for customers who have had
-- two appointments on the same day
FirstAppointments as (
    select
        CustomerId,
        Min(AppointmentId) AppointmentId,
        Min(AppointmentDate) AppointmentDate
    from
        AppointmentDates
    group by
        CustomerId
),

-- Second appointment date
SecondAppointments as (
    select
        AppointmentDates.CustomerId,
        Min(AppointmentDates.AppointmentId) AppointmentId,
        Min(AppointmentDates.AppointmentDate) AppointmentDate
    from
        AppointmentDates
        inner join FirstAppointments on AppointmentDates.CustomerId = FirstAppointments.CustomerId
    where
        AppointmentDates.AppointmentId > FirstAppointments.AppointmentId
    group by 
        AppointmentDates.CustomerId
    having
        Min(AppointmentDates.AppointmentDate) > @startDate
)

-- Bulk of query goes here; trimmed for brevity
select * from SecondAppointments

Answer 1

是的，使用 ROW_NUMBER() 你可以更容易地解决这个问题:

;WITH ranked AS (
  SELECT
    CustomerId,
    AppointmentId,
    AppointmentDate,
    VisitNumber = ROW_NUMBER() OVER (PARTITION BY CustomerId
                                         ORDER BY AppointmentDate)
  FROM AppointmentDates
)
SELECT
  CustomerId,
  AppointmentId,
  AppointmentDate
FROM ranked
WHERE VisitNumber     =  @visitNumber
  AND AppointmentDate >= @startDate
  AND AppointmentDate <  @endDate