SQL查询：判断输入年份是否在period字段字符串区间内并返回对应行

核心逻辑是将period字段按分隔符-拆分出起止年份，对无结束年份的情况（如2018-）将结束年份赋值为极大值9999，再判断用户输入年份是否落在区间内即可。

不同数据库实现示例 #

MySQL 版本 #

使用SUBSTRING_INDEX函数拆分字符串：

SELECT *
FROM cars
WHERE brand='Audi' 
  AND fuel='G' 
  AND <userIntroducedYear> BETWEEN 
      -- 提取起始年份
      CAST(SUBSTRING_INDEX(period, '-', 1) AS UNSIGNED) 
      AND 
      -- 提取结束年份，空值则替换为9999
      CASE 
        WHEN SUBSTRING_INDEX(period, '-', -1) = '' THEN 9999 
        ELSE CAST(SUBSTRING_INDEX(period, '-', -1) AS UNSIGNED) 
      END;

PostgreSQL 版本 #

使用split_part函数拆分字符串：

SELECT *
FROM cars
WHERE brand='Audi' 
  AND fuel='G' 
  AND <userIntroducedYear> BETWEEN 
      split_part(period, '-', 1)::INTEGER 
      AND 
      CASE 
        WHEN split_part(period, '-', 2) = '' THEN 9999 
        ELSE split_part(period, '-', 2)::INTEGER 
      END;

SQL Server 版本 #

使用CHARINDEX+LEFT/RIGHT拆分字符串：

SELECT *
FROM cars
WHERE brand='Audi' 
  AND fuel='G' 
  AND <userIntroducedYear> BETWEEN 
      CAST(LEFT(period, CHARINDEX('-', period)-1) AS INT)
      AND
      CASE
        WHEN RIGHT(period, LEN(period) - CHARINDEX('-', period)) = '' THEN 9999
        ELSE CAST(RIGHT(period, LEN(period) - CHARINDEX('-', period)) AS INT)
      END;

性能优化建议 #

如果该查询场景使用频率高，建议新增两个持久化生成列存储预计算的起止年份，还可以对这两个字段加索引大幅提升查询性能，以MySQL为例：

-- 新增生成列
ALTER TABLE cars 
ADD COLUMN start_year INT GENERATED ALWAYS AS (CAST(SUBSTRING_INDEX(period, '-', 1) AS UNSIGNED)) STORED,
ADD COLUMN end_year INT GENERATED ALWAYS AS (CASE WHEN SUBSTRING_INDEX(period, '-', -1) = '' THEN 9999 ELSE CAST(SUBSTRING_INDEX(period, '-', -1) AS UNSIGNED) END) STORED;

-- 后续查询直接使用预计算字段即可
SELECT * 
FROM cars 
WHERE brand='Audi' AND fuel='G' AND <userIntroducedYear> BETWEEN start_year AND end_year;

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