SQL Server：如何按指定年月筛选对应月份的活跃数据条目？

首先，我们需要明确：一个条目在指定月份内被视为活跃，意味着它的有效区间（从StartDate到EndDate，若EndDate为空则视为永久有效）与该月份的时间范围有重叠。结合你给出的活跃规则，我们可以把这个判断转化为精准的SQL日期范围条件。

核心逻辑推导 #

拿目标年月2017-10举例，先确定该月份的时间边界：

• 月份第一天：2017-10-01
• 月份最后一天：2017-10-31

活跃条目需要同时满足两个条件：

1. 条目开始时间不晚于月份最后一天（StartDate <= 月份最后一天）—— 确保条目在月份结束前已经启动
2. 条目结束时间晚于月份第一天（EndDate IS NULL OR EndDate > 月份第一天）—— 确保条目在月份开始后仍处于有效状态（或永久有效）

这两个条件结合，就能精准筛选出所有在该月份内至少有一天处于活跃状态的条目。

具体SQL实现（按数据库方言分类） #

下面是几种主流数据库的实现方式，假设我们要查询2017-10的活跃条目：

MySQL / MariaDB #

-- 先构造月份的首尾日期
SET @target_month = '2017-10';
SET @start_of_month = STR_TO_DATE(CONCAT(@target_month, '-01'), '%Y-%m-%d');
SET @end_of_month = LAST_DAY(@start_of_month);

-- 查询活跃条目
SELECT *
FROM your_table_name
WHERE StartDate <= @end_of_month
  AND (EndDate IS NULL OR EndDate > @start_of_month);

PostgreSQL #

WITH target_month AS (
  SELECT 
    DATE '2017-10-01' AS start_of_month,
    (DATE '2017-10-01' + INTERVAL '1 month' - INTERVAL '1 day')::DATE AS end_of_month
)
SELECT t.*
FROM your_table_name t
CROSS JOIN target_month tm
WHERE t.StartDate <= tm.end_of_month
  AND (t.EndDate IS NULL OR t.EndDate > tm.start_of_month);

SQL Server #

DECLARE @target_month VARCHAR(7) = '2017-10';
DECLARE @start_of_month DATE = CAST(@target_month + '-01' AS DATE);
DECLARE @end_of_month DATE = EOMONTH(@start_of_month);

SELECT *
FROM your_table_name
WHERE StartDate <= @end_of_month
  AND (EndDate IS NULL OR EndDate > @start_of_month);

进阶：一次性统计多个月份的活跃条目数 #

如果你想摆脱程序循环，直接一次性查询过去N个月的活跃条目统计，可以先生成一个年月序列，再关联你的表计算。以PostgreSQL为例：

-- 生成过去12个月的年月序列
WITH date_range AS (
  SELECT 
    DATE_TRUNC('month', CURRENT_DATE - INTERVAL 'n months')::DATE AS month_start
  FROM generate_series(0, 11) n
),
month_bounds AS (
  SELECT
    month_start,
    (month_start + INTERVAL '1 month' - INTERVAL '1 day')::DATE AS month_end,
    TO_CHAR(month_start, 'YYYY-MM') AS year_month
  FROM date_range
)
SELECT
  mb.year_month,
  COUNT(DISTINCT t.id) AS active_item_count -- 假设表有主键id
FROM month_bounds mb
LEFT JOIN your_table_name t
  ON t.StartDate <= mb.month_end
  AND (t.EndDate IS NULL OR t.EndDate > mb.month_start)
GROUP BY mb.year_month
ORDER BY mb.year_month DESC;

这样就能直接得到过去12个月每个月的活跃条目数量，无需在程序里循环处理每个年月。

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