如何调整MySQL查询以获取目标月份区间的前后行数据

期望输出 #

当前查询语句 #

SELECT TABLE_NAME,
       SUBSTRING(TIMESTAMPADD(MONTH,
                    CAST(SUBSTRING_INDEX(TABLE_NAME, '_', -1) AS UNSIGNED) - 595,
                    '2018-11-01'),1, 7) AS MONTH
                        
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'vbSuccessfulCDR_%'
AND SUBSTRING(TIMESTAMPADD(MONTH,
                    CAST(SUBSTRING_INDEX(TABLE_NAME, '_', -1) AS UNSIGNED) - 595,
                    '2018-11-01'),1, 7) BETWEEN '2019-03' AND '2019-05';

当前返回结果 #

可以通过以下两种方式修改查询，获取目标的前后行数据：

方式一：扩大日期范围 #

直接将BETWEEN的日期范围调整为'2019-02' AND '2019-06'，覆盖期望的月份区间：

SELECT TABLE_NAME,
       SUBSTRING(TIMESTAMPADD(MONTH,
                    CAST(SUBSTRING_INDEX(TABLE_NAME, '_', -1) AS UNSIGNED) - 595,
                    '2018-11-01'),1, 7) AS MONTH
                        
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'vbSuccessfulCDR_%'
AND SUBSTRING(TIMESTAMPADD(MONTH,
                    CAST(SUBSTRING_INDEX(TABLE_NAME, '_', -1) AS UNSIGNED) - 595,
                    '2018-11-01'),1, 7) BETWEEN '2019-02' AND '2019-06';

方式二：直接筛选表名后缀数字 #

由于表名的后缀数字和月份存在固定对应关系（598对应2019-02，602对应2019-06），可以直接基于表名的数字部分筛选，避免重复计算日期，效率更高：

SELECT TABLE_NAME,
       SUBSTRING(TIMESTAMPADD(MONTH,
                    CAST(SUBSTRING_INDEX(TABLE_NAME, '_', -1) AS UNSIGNED) - 595,
                    '2018-11-01'),1, 7) AS MONTH
                        
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'vbSuccessfulCDR_%'
AND CAST(SUBSTRING_INDEX(TABLE_NAME, '_', -1) AS UNSIGNED) BETWEEN 598 AND 602;

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