如何用单条SQL按年提取2010-2020年全年在职员工名单

核心逻辑完全等价于Cypher的年份UNWIND匹配：先构造2010-2020的年份集合，再与员工表做交叉匹配，最后过滤符合全年在职条件的记录，无需重复编写单年查询。

方案1：固定年份拼接（兼容性最优） #

直接通过VALUES/UNION ALL拼出2010-2020的年份列表，适配绝大多数数据库（包括老版本数据库）：

SELECT 
  y AS Year,
  employee_name
FROM (
  -- 通用数据库写法（PostgreSQL/MySQL 8+/SQL Server）
  VALUES (2010), (2011), (2012), (2013), (2014), (2015), (2016), (2017), (2018), (2019), (2020)
  AS years(y)

  -- 若为Oracle，替换上面的子查询为以下写法：
  -- SELECT 2010 AS y FROM DUAL UNION ALL
  -- SELECT 2011 AS y FROM DUAL UNION ALL
  -- SELECT 2012 AS y FROM DUAL UNION ALL
  -- SELECT 2013 AS y FROM DUAL UNION ALL
  -- SELECT 2014 AS y FROM DUAL UNION ALL
  -- SELECT 2015 AS y FROM DUAL UNION ALL
  -- SELECT 2016 AS y FROM DUAL UNION ALL
  -- SELECT 2017 AS y FROM DUAL UNION ALL
  -- SELECT 2018 AS y FROM DUAL UNION ALL
  -- SELECT 2019 AS y FROM DUAL UNION ALL
  -- SELECT 2020 AS y FROM DUAL
) years
CROSS JOIN employees
-- 日期转换函数根据所用数据库微调，核心逻辑为生成当年1月1日、12月31日
WHERE employment_start_date < CAST(y || '-01-01' AS DATE)
  AND employment_end_date > CAST(y || '-12-31' AS DATE)

方案2：递归CTE生成动态年份（适合灵活调整范围） #

如果后续需要调整年份范围，用标准递归CTE自动生成连续年份，无需手动拼接列表：

WITH RECURSIVE years(y) AS (
  -- 锚定起始年份
  SELECT 2010 AS y
  UNION ALL
  -- 递归生成下一年，直到终止年份2020
  SELECT y + 1 FROM years WHERE y < 2020
)
SELECT 
  y AS Year,
  employee_name
FROM years
CROSS JOIN employees
WHERE employment_start_date < DATE_FROM_PARTS(y, 1, 1)
  AND employment_end_date > DATE_FROM_PARTS(y, 12, 31)

适配说明 #

不同数据库的日期内置函数略有差异，只需替换日期生成部分即可：

• MySQL可用STR_TO_DATE(CONCAT(y, '-01-01'), '%Y-%m-%d')生成当年1月1日，STR_TO_DATE(CONCAT(y, '-12-31'), '%Y-%m-%d')生成当年12月31日
• Oracle可用TO_DATE(y || '-01-01', 'YYYY-MM-DD')、TO_DATE(y || '-12-31', 'YYYY-MM-DD')
• SQL Server可用DATEFROMPARTS(y, 1, 1)、DATEFROMPARTS(y, 12, 31)

用提供的样例数据表运行上述SQL，可直接得到期望的输出结果。

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