在第一行按日期显示的左联接

Question

如果'Math'或‘生物学’有标志为NULL或= ，则需要从下面留下与第一个出现的类(按日期)的联接：‘文学’E29或E 110‘英语文学’<代码>E 211。

如果'Math'或‘生物学’有标志= 1，那么我们需要与最近(根据日期)的类或‘英语文学’一起加入。

表首表：

| class              | date                    | flag |
| ------------------ | ----------------------- | ---- |
| Math               | 2020-07-07 20:08:00.000 | 0    |
| Biology            | 2020-07-07 21:08:00.000 | 1    |
| Math               | 2020-07-08 17:08:00.000 | NULL |
| English            | 2020-07-10 13:08:00.000 | 0    |
| Literature         | 2020-07-15 20:08:00.000 |      |
| English Literature | 2020-07-15 21:08:00.000 |      |
| Math               | 2020-07-15 22:08:00.000 | 1    |
| Literature         | 2020-09-09 20:08:00.000 |      |
| Math               | 2020-09-16 11:08:00.000 | 1    |
| English            | 2020-09-17 13:18:00.000 | 0    |
| Biology            | 2020-09-19 13:18:00.000 | NULL |

结果表(包含没有类的类=“文学”，“英语文学”)：

| class   | date                    | flag | class_2            | date_2                  |
| ------- | ----------------------- | ---- | ------------------ | ----------------------- |
| Math    | 2020-07-07 20:08:00.000 | 0    | Literature         | 2020-07-15 20:08:00.000 |
| Biology | 2020-07-07 21:08:00.000 | 1    |                    |                         |
| Math    | 2020-07-08 17:08:00.000 | NULL | Literature         | 2020-07-15 20:08:00.000 |
| English | 2020-07-10 13:08:00.000 | 0    |                    |                         |
| Math    | 2020-07-15 22:08:00.000 | 1    | English Literature | 2020-07-15 21:08:00.000 |
| Math    | 2020-09-16 11:08:00.000 | 1    | Literature         | 2020-09-09 20:08:00.000 |
| English | 2020-09-17 13:18:00.000 | 0    |                    |                         |
| Biology | 2020-09-19 13:18:00.000 | NULL |                    |                         |

解释：

1. Biology at 2020-09-19 13:18:00.000没有连接，因为它的标志为NULL及以下(比‘生物学’还要晚)根据rules.
2. Biology at 2020-07-07 21:08:00.000与标志1E 243也没有连接因为没有“英语文学”或“文学”在

之前

3) 在2020-07-07 20:08:00.000使用标志0 (这是第一行)。由于旗子是0，所以我们看第一堂课后出现的“文学”或“英国文学”课。第一次出现的是2020年-07-15 20:08:00.000的“文学”，所以我们匹配它们。

如何根据日期查找最近行或第一行的左联接？

Answer 1

没有声称这是最优雅的解决方案；这将有效：

with Tbl as (
    select Class, Date=cast(date as datetime), flag
    from (values
     ('Math',' 2020-07-07 20:08:00.000 ', 0    )
    ,('Biology',' 2020-07-07 21:08:00.000 ', 1    )
    ,('Math',' 2020-07-08 17:08:00.000 ', NULL )
    ,('English',' 2020-07-10 13:08:00.000 ', 0    )
    ,('Literature',' 2020-07-15 20:08:00.000 ',NULL      )
    ,('English Literature',' 2020-07-15 21:08:00.000 ',NULL      )
    ,('Math',' 2020-07-15 22:08:00.000 ', 1    )
    ,('Literature',' 2020-09-09 20:08:00.000 ',NULL      )
    ,('Math',' 2020-09-16 11:08:00.000 ', 1    )
    ,('English',' 2020-09-17 13:18:00.000 ', 0    )
    ,('Biology',' 2020-09-19 13:18:00.000 ', NULL )
    ) T(Class, date, flag)
),
T as (
 select 
       Tbl.*
     , Case when Class in ('Math','Biology') then 'MB' 
            when Class in ('Literature','English Literature') then 'LE'
            else 'Others'
       end as ClassGroup
 from Tbl
)
select *
from (select Ta.*
             , Tb.Class as B_Class
             , Tb.date as B_Date
             , Tb.flag as B_Flag
             , SeqAsc=row_number() over (partition by ta.ClassGroup, ta.Date order by tb.Date asc) 
             , SeqDesc=row_number() over (partition by ta.ClassGroup, ta.Date order by tb.Date desc) 
        from T as Ta
             left join
             T as Tb
             on Ta.ClassGroup='MB'
                and Tb.ClassGroup='LE'
                and ((coalesce(Ta.flag,0)=0 and Ta.date <= Tb.date) 
                     OR
                     (coalesce(Ta.flag,0)=1 and Ta.date >= Tb.date)
                    )
        where Ta.ClassGroup in ('MB','Others')
     ) T
where 
 (coalesce(T.flag,0)=0 and SeqAsc=1)
 or (coalesce(T.flag,0)=1 and SeqDesc=1)
 or B_Class is null
 order by Date, class

您有一些空标志值，我假设为NULL，您没有指示您使用了哪些数据库产品，我假设是SqlServer (但是它应该适用于大多数数据库产品)。