如何用SQL语句查询最近（正在进行或即将开始）的团购列表信息？

如何使用 sql 查询最近团购列表信息

对于你的问题，我们需要明确以下两点：

1. 如果存在正在进行的团购，则查询正在进行的团购。
2. 如果不存在正在进行的团购，则查询离当前时间最近、即将开始的团购（而不是所有未开始的团购）。

sql 查询语句：

select
    t4.*,
    now() as 当前时间
from
(
    select
        substring_index(
            substring_index(a.team_id_list, ',', b.help_topic_id + 1),
            ',',
            -1
        ) as team_id
    from
    (
        select
            team_id_list
        from
        (
            select
                group_concat(team_id) as team_id_list
            from
                team_found
            where
                team_start_time <= now() and team_end_time > now()
            union all
            select
                team_id_list
            from
            (
                select
                    group_concat(team_id) as team_id_list
                from
                    team_found
                where
                    team_start_time > now()
                group by
                    team_start_time,
                    team_end_time
                limit 1
            ) t0
        ) t1
        where
            t1.team_id_list is not null
        limit 1
    ) t2
    join mysql.help_topic b on b.help_topic_id < (
        length(a.team_id_list) - length(replace(a.team_id_list, ',', '')) + 1
    )
) t3
left join team_found t4 on t3.team_id = t4.team_id;

查询结果示例：

SmartWriter.ai

用 AI生成个性化电子邮件或Linkedin消息

下载

现在时间为2021-3-23 10：21：30

+--------+--------+---------------------+---------------------+----------+------------+
| team_id | prod_id | team_start_time      | team_end_time        | prod_name | 当前时间  |
+--------+--------+---------------------+---------------------+----------+------------+
|      2 |      1 | 2021-03-23 11:00:00 | 2021-03-23 11:30:00 | 猪       | 2021-03... |
+--------+--------+---------------------+---------------------+----------+------------+

现在时间为2021-3-23 12：05：05

+--------+--------+---------------------+---------------------+----------+------------+
| team_id | prod_id | team_start_time      | team_end_time        | prod_name | 当前时间  |
+--------+--------+---------------------+---------------------+----------+------------+
|      3 |      2 | 2021-03-23 12:00:00 | 2021-03-23 12:30:00 | 狗       | 2021-03... |
+--------+--------+---------------------+---------------------+----------+------------+

现在时间为2021-3-23 21：31：00

+--------+--------+---------------------+---------------------+----------+------------+
| team_id | prod_id | team_start_time      | team_end_time        | prod_name | 当前时间  |
+--------+--------+---------------------+---------------------+----------+------------+
|     15 |      8 | 2021-03-24 11:00:00 | 2021-03-24 11:30:00 | 兔       | 2021-03... |
|     16 |      8 | 2021-03-24 11:00:00 | 2021-03-24 11:30:00 | 兔       | 2021-03... |
+--------+--------+---------------------+---------------------+----------+------------+