基于TRAN_YRMO字段拆分年月并生成最大年份列的SQL问题

需要将存储格式为YYYYMM（例如202304）的[TRAN_YRMO]字段拆分为年份列和月份列，同时新增一列存储全局最大年份（所有行取值相同）。

基础查询语句 #

SELECT 
       [TNET]
      ,[SORTCODE]    
      ,[LOADDATE]
      ,[TRAN_YRMO]
  FROM [dbo].[tbl_941_Monthly_Cumulative]

报错的尝试代码 #

SELECT c.*, c.[year]
from  c
LEFT JOIN b
ON c.[year] = b.[Max Year]

(SELECT
*,
LEFT([TRAN_YRMO],4) as 'Year' ,
RIGHT([TRAN_YRMO],2) as 'Month'
FROM [dbo].[tbl_941_Monthly_Cumulative])c

(SELECT
MAX(a.[year]) AS 'Max Year'

from 

(SELECT *,
LEFT([TRAN_YRMO],4) as 'Year' ,
RIGHT([TRAN_YRMO],2) as 'Month'
FROM [dbo].[tbl_941_Monthly_Cumulative])a)b

期望结果 #

正确的SQL实现方案 #

方案一：子查询交叉关联 #

使用CROSS JOIN将主查询结果与全局最大年份的子查询关联，确保每行都带上最大值：

SELECT 
    c.TNET,
    c.SORTCODE,
    c.LOADDATE,
    c.TRAN_YRMO,
    c.[Year],
    c.[Month],
    b.[Max Year]
FROM (
    SELECT 
        *,
        LEFT([TRAN_YRMO], 4) AS [Year],
        RIGHT([TRAN_YRMO], 2) AS [Month]
    FROM [dbo].[tbl_941_Monthly_Cumulative]
) c
CROSS JOIN (
    SELECT MAX(LEFT([TRAN_YRMO], 4)) AS [Max Year]
    FROM [dbo].[tbl_941_Monthly_Cumulative]
) b

方案二：窗口函数（推荐） #

利用窗口函数MAX() OVER()直接计算全局最大值，代码更简洁高效：

SELECT 
    [TNET],
    [SORTCODE],
    [LOADDATE],
    [TRAN_YRMO],
    LEFT([TRAN_YRMO], 4) AS [Year],
    RIGHT([TRAN_YRMO], 2) AS [Month],
    MAX(LEFT([TRAN_YRMO], 4)) OVER() AS [Max Year]
FROM [dbo].[tbl_941_Monthly_Cumulative]

原代码错误说明 #

1. 子查询定义顺序错误：需先定义子查询c和b，再在FROM子句中引用
2. JOIN逻辑错误：使用LEFT JOIN并关联年份等于最大值，会导致仅匹配年份行显示最大值，不符合需求
3. 字段选择错误：误选c.[year]而非b.[Max Year]作为全局最大年份列

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