需求介绍

有一场线上展会，有很多参展商参加，要求直播进行中的排序在前，其他按照sort排序。

字段概要

create table online_consultation_meeting_exhibitor
(
    id                             bigint unsigned auto_increment
        primary key,
  exhibitor_id                   int unsigned  not null comment '展商编号',
    sort                           int default 1 not null comment '排序',
    live_time_start                time          null comment '直播开始时间',
    live_time_end                  time          null comment '直播结束时间',
    exhibition_date                date          null comment '展会日期'
)
    comment '线上展商' collate = utf8mb4_unicode_ci;

难点

如果判断进行中的线上展商，可以通过case when,或者三元表达式判断

代码

select
  id,
  live_time_start,
  live_time_end,
  exhibition_date,
  IF(
    concat(exhibition_date, ' ', live_time_start) <= current_timestamp
    and current_timestamp <= concat(exhibition_date, ' ', live_time_end),
    1,
    2
  ) as time_sort
from
  `online_consultation_meeting_exhibitor`
order by
  `time_sort` ,
  `sort` desc