SQL Server查询Table1中WEEKS日期大于子查询结果时报"')'附近语法错误"

• 语法错误触发点为派生表无别名的冗余嵌套括号：原查询在FROM子句后额外嵌套了一层包裹派生表x的括号，该层括号作为最外层派生表未指定别名，违反SQL Server「FROM后所有派生表必须显式声明别名」的规则，解析到闭合括号时直接抛出')'附近的语法错误。
• 隐含歧义：Table1的筛选条件中CHARINDEX的WEEKS列未加表别名a，多表关联场景下会触发列名歧义问题。
• 冗余嵌套：UNION ALL两侧单独取MAX的逻辑可合并，减少查询层级。

SELECT * 
FROM Table1 a 
WHERE CONVERT(DATE, SUBSTRING(a.WEEKS, CHARINDEX('W/E', a.WEEKS) + 4, 12), 1) >
(
    SELECT MAX(x.WEEKS) AS MONTHLY_MAX_WEEKS
    FROM
    (
        SELECT MAX(CONVERT(DATE, SUBSTRING(WEEKS, CHARINDEX('W/E', WEEKS) + 4, 12), 1)) AS WEEKS  
        FROM Table2 
        UNION ALL
        SELECT MAX(CONVERT(DATE, SUBSTRING(WEEKS, CHARINDEX('W/E', WEEKS) + 4, 12), 1)) AS WEEKS 
        FROM Table3
    ) AS x
)

如果不需要分别取Table2、Table3的全局最大周日期再合并，可直接对两表原始周值转换后再求MAX，减少计算层级：

SELECT * 
FROM Table1 a 
WHERE CONVERT(DATE, SUBSTRING(a.WEEKS, CHARINDEX('W/E', a.WEEKS) + 4, 12), 1) >
(
    SELECT MAX(CONVERT(DATE, SUBSTRING(WEEKS, CHARINDEX('W/E', WEEKS) + 4, 12), 1))
    FROM
    (
        SELECT WEEKS FROM Table2 
        UNION ALL
        SELECT WEEKS FROM Table3
    ) AS x
)

提示：如果转换后日期出现解析异常，可调整CONVERT的第三个样式参数，比如对应yyyy-mm-dd格式改为120，对应mm/dd/yyyy格式改为101。

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