如何实现SQL多表关联结果的多优先级报名周期排序

当前需要处理的是复杂SQL查询的结果排序问题，底层数据由3张关联表构成，用于存储不同活动信息及其对应的报名周期映射关系。

基础查询语句 #

以下语句是当前复杂查询的基础组成部分，通过三表联查取出需要的核心字段：

select 
    act.ID, act.Name, arp.RegistrationPeriodId, 
    rp.StartDateTime, rp.EndDateTime 
from
    Activity act
join 
    ActivityRegistrationPeriod arp on arp.ActivityId = act.ID
join
    RegistrationPeriod rp on rp.Id = arp.RegistrationPeriodId

排序规则要求 #

需要按照以下优先级对查询结果完成排序：

• 最高优先级：优先展示当前处于开放报名阶段的项目，判断逻辑为当前日期落在报名周期的StartDateTime与EndDateTime区间内
• 次高优先级：展示报名即将截止的项目，判断逻辑为报名周期EndDateTime距离当前日期越近，排序越靠前
• 最低优先级：最后展示报名即将开放的项目，判断逻辑为报名周期StartDateTime距离当前日期越近，排序越靠前

此前曾尝试通过创建临时表，将符合各条件的数据分别存储后再拼接的方式实现需求，但该方法的运行效果不符合预期，因此希望探索无需拆分临时表、直接通过排序逻辑实现目标的可行方案。

当前已尝试的带排序逻辑的查询版本 #

目前已经在基础查询上添加了排序逻辑，对应SQL如下：

select 
    act.ID, act.Name, arp.RegistrationPeriodId, 
    rp.StartDateTime, rp.EndDateTime 
from
    Activity act
join 
    ActivityRegistrationPeriod arp on arp.ActivityId = act.ID
join
    RegistrationPeriod rp on rp.Id = arp.RegistrationPeriodId
--where 
--  act.AccountId = 3106
order by 
    (case when GETDATE() between StartDateTime and endDateTime then 1 else 2 end),
    (case when rp.EndDateTime is null then 2 else 1 end), 
    rp.EndDateTime asc,
    (case when rp.StartDateTime is null then 2 else 1 end), 
    rp.StartDateTime desc

内容的提问来源于stack exchange，提问作者Bharat