mysql - 匹配日期的最后一个实例的 SQL 查询

Question

我们有一个系统，可以为每个客户存储有关在特定日期商定的行动计划的详细信息，并且可以在该日期或之后将其输入系统。每个计划自同意之日起至下一个计划同意之日有效。一位客户可以在同一天拥有多个计划。

数据存储在 SQL 数据库中，看起来像这样(假设只有一个客户端):

PlanID  ClientID    PlanDate        EntryDateTime           (other fields)
1       1           2019-01-01      2019-01-01 12:53:01     ABC...
2       1           2019-01-11      2019-01-12 09:18:23     DEF...
3       1           2019-01-21      2019-01-21 14:02:11     GHI...
4       1           2019-01-21      2019-01-22 08:32:58     JKL...
5       1           2019-01-31      2019-02-01 11:47:18     MNO...

随着时间的推移，我们需要运行有关行动计划内容的报告。出于报告的目的，我们需要知道每个给定计划的开始和结束日期以及同一天制定多个计划的位置，然后我们只关心该计划的最终实例。

鉴于上述情况，我们希望输出看起来像这样:

PlanID  ClientID    StartDate       EndDate         (other fields)
1       1           2019-01-01      2019-01-11      ABC...
2       1           2019-01-11      2019-01-21      DEF...
4       1           2019-01-21      2019-01-31      JKL...
5       1           2019-01-31      NULL            MNO...

但是，最接近我的是以下查询:

SELECT 
    OuterTable.*,

    (SELECT 
        MIN(PlanDate) AS PlanDate 
        FROM tblPlans
        WHERE
            ClientID = OuterTable.ClientID
            AND PlanDate > OuterTable.PlanDate
    ) AS PlanEndDate,

FROM
    tblPlans AS OuterTable;

结果如下:

PlanID  ClientID    PlanDate        PlanEndDate     (other fields)
1       1           2019-01-01      2019-01-11      ABC...
2       1           2019-01-11      2019-01-21      DEF...
3       1           2019-01-21      2019-01-31      GHI...
4       1           2019-01-21      2019-01-31      JKL...
5       1           2019-01-31      NULL            MNO...

换句话说，行数据是正确的，但结果集包括1月21日的两条记录。

我如何调整查询以便只包含给定 PlanDate 的最近输入的记录。

理想情况下，任何解决方案都将使用通用(可移植)SQL，但出于项目的实际目的，我需要能够在 MySQL(v5.6 及更高版本)和 MS SQL Server(2014 及更高版本)上运行的东西。

Answer 1

试试这个:

with

filtered as (
    select
        ClientID,
        PlanDate,
        max(EntryDateTime) as max_entry_datetime
    from
        tblPlans
    group by
        ClientID,
        PlanDate
)

SELECT
    p.PlanID,
    p.ClientID,
    p.PlanDate as StartDate,
    min(f2.PlanDate) as EndDate
FROM
    tblPlans as p
    join filtered as f on   p.ClientID = f.ClientID
                            and p.PlanDate = f.PlanDate
                            and p.EntryDateTime = f.max_entry_datetime
    left join filtered as f2 on p.ClientID = f2.ClientID
                                and p.PlanDate < f2.PlanDate
group by
    p.PlanID,
    p.ClientID,
    p.PlanDate

或使用窗口函数(如果可以使用它们):

SELECT
    p.PlanID,
    p.ClientID,
    p.PlanDate as StartDate,
    lead(p.PlanDate) over(  partition by
                                p.ClientID
                            order by
                                p.PlanDate) as EndDate
FROM
    (   select
            *,
            row_number() over(  partition by
                                    ClientID,
                                    PlanDate
                                order by
                                    EntryDateTime desc) as rn
        from
            tblPlans) as p
where
    rn = 1

对于 MySQL 5，我认为这个可以工作:

SELECT
    p.PlanID,
    p.ClientID,
    p.PlanDate as StartDate,
    min(f2.PlanDate) as EndDate
FROM
    tblPlans as p
    join (  select
                ClientID,
                PlanDate,
                max(EntryDateTime) as max_entry_datetime
            from
                tblPlans
            group by
                ClientID,
                PlanDate) as f on   p.ClientID = f.ClientID
                                    and p.PlanDate = f.PlanDate
                                    and p.EntryDateTime = f.max_entry_datetime
    left join ( select
                    ClientID,
                    PlanDate,
                    max(EntryDateTime) as max_entry_datetime
                from
                    tblPlans
                group by
                    ClientID,
                    PlanDate) as f2 on  p.ClientID = f2.ClientID
                                        and p.PlanDate < f2.PlanDate
group by
    p.PlanID,
    p.ClientID,
    p.PlanDate