如何正确结束CAST嵌套的CASE语句？报错‘varchar位置无效’

你的CASE语句已经用END正确结束了，报错varchar is not valid at this position的核心原因是CASE表达式返回的类型不统一，以及后续条件判断的类型不匹配问题：

• 当age为'NA'时，CASE返回字符串'0'；如果age字段本身是数值类型，ELSE分支直接返回数值类型的age，两种类型混合导致数据库无法解析CAST操作，触发语法错误。
• 即使CAST成功，后续用字符串类型的Age和数值60比较，会按字符串规则排序（比如'100'会小于'60'），得到错误的查询结果。

修正后的SQL代码 #

WITH t1 AS
     (SELECT Name, Sex, Team, Games, City, sport, event, medal,
             CAST(CASE WHEN age = 'NA' THEN '0' ELSE CAST(age AS VARCHAR(20)) END AS VARCHAR(20)) AS age
      FROM athlete_events ),
     t2 AS
     (SELECT * FROM t1 WHERE Medal IN ('Gold','Silver'))
SELECT * FROM t2 WHERE CAST(Age AS INTEGER) >= 60

简化写法（可选） #

如果你的数据库支持NULLIF和COALESCE函数，可以把'NA'转成NULL后再替换为'0'，代码更简洁：

WITH t1 AS
     (SELECT Name, Sex, Team, Games, City, sport, event, medal,
             CAST(COALESCE(NULLIF(age, 'NA'), '0') AS VARCHAR(20)) AS age
      FROM athlete_events ),
     t2 AS
     (SELECT * FROM t1 WHERE Medal IN ('Gold','Silver'))
SELECT * FROM t2 WHERE CAST(Age AS INTEGER) >= 60

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