如何在SQL中添加列判断指定月份是否在两个日期区间内

需求说明 #

需要在SQL查询结果中为每个2023年的月份新增一列，若该月份处于当前行的Date from与Date to日期区间内，则列值为1，否则为0。

现有表格数据 #

期望查询结果 #

解决方案 #

1. 静态列实现（已知月份范围） #

如果需要生成的月份是固定的（比如仅2023年全年），可以直接用CASE WHEN语句逐个判断每个月份是否在区间内：

SELECT
  `Customer nr`,
  `Date from`,
  `Date to`,
  -- 判断2023.01是否在区间内
  CASE
    WHEN DATE_FORMAT(`Date from`, '%Y-%m') <= '2023-01' AND DATE_FORMAT(`Date to`, '%Y-%m') >= '2023-01' THEN 1
    ELSE 0
  END AS `2023.01`,
  -- 判断2023.02是否在区间内
  CASE
    WHEN DATE_FORMAT(`Date from`, '%Y-%m') <= '2023-02' AND DATE_FORMAT(`Date to`, '%Y-%m') >= '2023-02' THEN 1
    ELSE 0
  END AS `2023.02`,
  -- 判断2023.03是否在区间内
  CASE
    WHEN DATE_FORMAT(`Date from`, '%Y-%m') <= '2023-03' AND DATE_FORMAT(`Date to`, '%Y-%m') >= '2023-03' THEN 1
    ELSE 0
  END AS `2023.03`,
  -- 依次添加2023.04至2023.11的判断语句
  -- 判断2023.12是否在区间内
  CASE
    WHEN DATE_FORMAT(`Date from`, '%Y-%m') <= '2023-12' AND DATE_FORMAT(`Date to`, '%Y-%m') >= '2023-12' THEN 1
    ELSE 0
  END AS `2023.12`
FROM your_table_name;

说明：

• DATE_FORMAT函数用于将日期转换为YYYY-MM格式，方便与目标月份字符串比较（适用于MySQL）。
• 若使用SQL Server，可替换为CONVERT(varchar(7), [Date from], 120)；若使用PostgreSQL，可替换为TO_CHAR("Date from", 'YYYY-MM')。

2. 动态列实现（月份范围不确定） #

如果需要生成的月份范围不固定，可以通过动态SQL自动生成所有需要的月份列：

-- MySQL动态SQL示例
SET @sql = NULL;
-- 生成2023年所有月份的YYYY-MM格式字符串
SELECT
  GROUP_CONCAT(
    DISTINCT
    CONCAT(
      'CASE WHEN DATE_FORMAT(`Date from`, ''%Y-%m'') <= ''', month_str, ''' AND DATE_FORMAT(`Date to`, ''%Y-%m'') >= ''', month_str, ''' THEN 1 ELSE 0 END AS `', REPLACE(month_str, '-', '.'), '`'
    )
  ) INTO @sql
FROM (
  SELECT DATE_FORMAT(date_add('2023-01-01', interval t.n month), '%Y-%m') AS month_str
  FROM (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
  ) t
) months;

-- 拼接完整查询语句
SET @sql = CONCAT('SELECT `Customer nr`, `Date from`, `Date to`, ', @sql, ' FROM your_table_name');

-- 执行动态查询
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

说明：

• 这段代码会自动生成2023年12个月份的判断列，若需要调整年份，修改date_add中的起始日期即可。
• 不同数据库的动态SQL语法略有差异，需根据使用的数据库类型调整。

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