如何使用 sql 查询最近团购列表信息
对于你的问题，我们需要明确以下两点：
- 如果存在正在进行的团购，则查询正在进行的团购。
- 如果不存在正在进行的团购，则查询离当前时间最近、即将开始的团购（而不是所有未开始的团购）。
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;
登录后复制
查询结果示例：
现在时间为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... | +--------+--------+---------------------+---------------------+----------+------------+
登录后复制
以上就是如何用SQL语句查询最近（正在进行或即将开始）的团购列表信息？的详细内容，更多请关注php中文网其它相关文章！