时间戳

首页 > 解决方案 > 5.1 版的 MySQL 查询在 5.7 中不起作用

mysql - 5.1 版的 MySQL 查询在 5.7 中不起作用

问题描述

select * from batches join (
                  select * from (
                      select *, row_number() over (
                          partition by batch_id
                          order by date ASC
                      ) as row_num
                      from batch_schedule where display = 'Yes'
                  ) as d_schedule
                  where d_schedule.row_num = 1
              )
              as f_schedule
              on batches.id = f_schedule.batch_id
              WHERE (f_schedule.date BETWEEN '2021/03/01' AND '2021/04/30')

此查询在 5.1 版中运行良好，但在 5.7 版中无法运行

我想要实现的是，根据 batch_schedule 开始日期过滤批次。

我需要从右表中选择第一行，条件显示 = 'Yes' 并按日期 asc 排序，并基于该行值我需要显示批次。

我收到以下错误消息

select is not valid at this position for this server version expecting '(' with

标签： mysql

解决方案

select * from batches join ( SELECT * from batch_schedule 
where display = 'Yes' group by batch_id order by date asc ) 
as f_schedule on batches.id = f_schedule.batch_id 
where(f_schedule.date BETWEEN '2021/03/01' AND '2021/04/30');

我试过这个并且它有效。我在这里发帖以防万一有人在寻找相同的东西会有所帮助。

推荐阅读