SQL语法错误修复咨询：CASE语句问题、[3:3]含义及WITH子句用法

问题描述 #

运行以下SQL代码时出现错误提示：Syntax error: Expected keyword ELSE or keyword END but got keyword CASE at [3:3]，修改别名Plan_category为Charge_category后问题仍未解决，咨询以下问题：

1. 如何修复该语法错误？
2. 错误信息末尾的[3:3]代表什么含义？
3. 若同时使用WITH子句，是否存在用法差异？

出错的SQL代码：

SELECT *,
  CASE WHEN Charge='Free week extension (0.00)' THEN 'Free extension'
  CASE WHEN Charge='Free week extension (Free week extension) (x1) 0.00' THEN 'Free extension'
  CASE WHEN Charge='Free week extension'THEN 'Free extension'
  CASE WHEN Charge='1 WEEK FREE TRIAL / 1주 무료체험권 (0.00)' THEN 'Free trial'
  CASE WHEN Charge='1 WEEK FREE TRIAL / 1주 무료체험권 (1주 무료체험권 ( 1 WEEK FREE TRIAL )) (x1) 0.00' THEN 'Free trial'
  CASE WHEN Charge='Free Trial' THEN 'Free trial'
  CASE WHEN Charge='1 WEEK FREE TRIAL / 1주 무료체험권' THEN 'Free trial'
  CASE WHEN Charge='Foundation Membership' THEN 'Foundation 1 month'
  CASE WHEN Charge='Foundation Membership' THEN 'Foundation 1 month'
  CASE WHEN Charge='Foundation Membership (0.00)' THEN 'Foundation 1 month'
  CASE WHEN Charge='Foundation Membership (295800.00)' THEN 'Foundation 1 month'
  CASE WHEN Charge='Foundation Membership (Foundation Membership - 1 month) (x1) 147900.00' THEN 'Foundation 1 month'
  CASE WHEN Charge='Foundation Membership (Foundation Membership - 1 month) (x1) 295800.00' THEN 'Foundation 1 month'
  CASE WHEN Charge='Foundation Membership (835200.00)' THEN 'Foundation 3 month'
  CASE WHEN Charge='Foundation Membership (Foundation Membership 3-month) (x1) 835200.00' THEN 'Foundation 3 month'
  CASE WHEN Charge='CLASS PASSES / 회수권' THEN '1 Class Pass'
  CASE WHEN Charge='CLASS PASSES / 회수권 (30000.00)' THEN '1 Class Pass'
  CASE WHEN Charge='CLASS PASSES / 회수권 (1 Class Pass / 1회 이용권) (x1) 30000.00' THEN '1 Class Pass'
  CASE WHEN Charge='CLASS PASSES / 회수권 (250000.00)' THEN '10 Class Pass'
  CASE WHEN Charge='CLASS PASSES / 회수권 (10 Class Pass / 10회 이용권) (x1) 125000.00' THEN '10 Class Pass'
  CASE WHEN Charge='CLASS PASSES / 회수권 (10 Class Pass / 10회 이용권) (x1) 200000.00' THEN '10 Class Pass'
  CASE WHEN Charge='CLASS PASSES / 회수권 (10 Class Pass / 10회 이용권) (x1) 250000.00' THEN '10 Class Pass'
  CASE WHEN Charge='CLASS PASSES / 회수권 (20 Class Pass / 20회 이용권) (x1) 400000.00' THEN '20 Class Pass'
  CASE WHEN Charge='CLASS PASSES / 회수권 (20 Class Pass / 20회 이용권) (x1) 450000.00' THEN '20 Class Pass'
  CASE WHEN Charge='CLASS PASSES / 회수권 (450000.00)' THEN '20 Class Pass'
  CASE WHEN Charge='CLASS PASSES / 회수권 (40 Class Pass / 40회 이용권) (x1) 800000.00' THEN '40 Class Pass'
  CASE WHEN Charge='CLASS PASSES / 회수권 (800000.00)' THEN '40 Class Pass'
  CASE WHEN Charge='Influencer membership' THEN 'Influencer membership'
  CASE WHEN Charge='Influencer membership (Influencer membership (3 month)) (x1) 0.00' THEN 'Influencer membership'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권' THEN '1 Month Unlimited'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (348000.00)' THEN '1 Month Unlimited'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (1 MONTH UNLIMITED / 1개월 무제한 사용권) (x1) 0.00' THEN '1 Month Unlimited'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (1 MONTH UNLIMITED / 1개월 무제한 사용권) (x1) 298000.00' THEN '1 Month Unlimited'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (1 MONTH UNLIMITED / 1개월 무제한 사용권) (x1) 348000.00' THEN '1 Month Unlimited'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (835000.00)' THEN '3 Month Unlimited'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (887000.00)' THEN '3 Month Unlimited'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (3 MONTH UNLIMITED / 3개월 무제한 사용권 / (15% 혜택) (15% DISCOUNT)) (x1) 0.00' THEN '3 Month Unlimited'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (3 MONTH UNLIMITED / 3개월 무제한 사용권 / (15% 혜택) (15% DISCOUNT)) (x1) 473500.00' THEN '3 Month Unlimited'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (3 MONTH UNLIMITED / 3개월 무제한 사용권 / (15% 혜택) (15% DISCOUNT)) (x1) 798300.00' THEN '3 Month Unlimited'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (3 MONTH UNLIMITED / 3개월 무제한 사용권 / (15% 혜택) (15% DISCOUNT)) (x1) 857000.00' THEN '3 Month Unlimited'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (3 MONTH UNLIMITED / 3개월 무제한 사용권 / (15% 혜택) (15% DISCOUNT)) (x1) 877000.00' THEN '3 Month Unlimited'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (3 MONTH UNLIMITED / 3개월 무제한 사용권 / (15% 혜택) (15% DISCOUNT)) (x1) 887000.00' THEN '3 Month Unlimited'
  CASE WHEN Charge='MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (6 MONTH UNLIMITED / 6개월 무제한 사용권 / (20% 혜택) (20% DISCOUNT)) (x1) 1670000.00' THEN '6 Month Unlimited'
  CASE WHEN Charge='45일 챌린지 / 45 DAY CHALLENGE' THEN 'Challenge'
  CASE WHEN Charge='Challenge package (Challenge package) (x1) 746000.00' THEN 'Challenge'
  CASE WHEN Charge='2WEEK INITIATOR CHALLENGE (2WEEK INITIATOR CHALLENGE) (x1) 0.00' THEN 'Challenge' ELSE 'etc' END AS Plan_category FROM `F45_KR.GDE_transaction_W4_Oct_2022`

问题解答 #

1. 修复语法错误 #

你的核心问题是错误地使用了多个独立的CASE语句，SQL的CASE表达式语法要求：单个CASE开头，后续用WHEN...THEN...串联所有条件，最后用ELSE（可选）和END收尾，不能每个条件都写一个CASE。

同时可以把同一分类的Charge值用IN合并，简化代码结构。修正后的SQL如下：

SELECT *,
  CASE 
    WHEN Charge IN ('Free week extension (0.00)', 'Free week extension (Free week extension) (x1) 0.00', 'Free week extension') THEN 'Free extension'
    WHEN Charge IN ('1 WEEK FREE TRIAL / 1주 무료체험권 (0.00)', '1 WEEK FREE TRIAL / 1주 무료체험권 (1주 무료체험권 ( 1 WEEK FREE TRIAL )) (x1) 0.00', 'Free Trial', '1 WEEK FREE TRIAL / 1주 무료체험권') THEN 'Free trial'
    WHEN Charge IN ('Foundation Membership', 'Foundation Membership (0.00)', 'Foundation Membership (295800.00)', 'Foundation Membership (Foundation Membership - 1 month) (x1) 147900.00', 'Foundation Membership (Foundation Membership - 1 month) (x1) 295800.00') THEN 'Foundation 1 month'
    WHEN Charge IN ('Foundation Membership (835200.00)', 'Foundation Membership (Foundation Membership 3-month) (x1) 835200.00') THEN 'Foundation 3 month'
    WHEN Charge IN ('CLASS PASSES / 회수권', 'CLASS PASSES / 회수권 (30000.00)', 'CLASS PASSES / 회수권 (1 Class Pass / 1회 이용권) (x1) 30000.00') THEN '1 Class Pass'
    WHEN Charge IN ('CLASS PASSES / 회수권 (250000.00)', 'CLASS PASSES / 회수권 (10 Class Pass / 10회 이용권) (x1) 125000.00', 'CLASS PASSES / 회수권 (10 Class Pass / 10회 이용권) (x1) 200000.00', 'CLASS PASSES / 회수권 (10 Class Pass / 10회 이용권) (x1) 250000.00') THEN '10 Class Pass'
    WHEN Charge IN ('CLASS PASSES / 회수권 (20 Class Pass / 20회 이용권) (x1) 400000.00', 'CLASS PASSES / 회수권 (20 Class Pass / 20회 이용권) (x1) 450000.00', 'CLASS PASSES / 회수권 (450000.00)') THEN '20 Class Pass'
    WHEN Charge IN ('CLASS PASSES / 회수권 (40 Class Pass / 40회 이용권) (x1) 800000.00', 'CLASS PASSES / 회수권 (800000.00)') THEN '40 Class Pass'
    WHEN Charge IN ('Influencer membership', 'Influencer membership (Influencer membership (3 month)) (x1) 0.00') THEN 'Influencer membership'
    WHEN Charge IN ('MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권', 'MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (348000.00)', 'MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (1 MONTH UNLIMITED / 1개월 무제한 사용권) (x1) 0.00', 'MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (1 MONTH UNLIMITED / 1개월 무제한 사용권) (x1) 298000.00', 'MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (1 MONTH UNLIMITED / 1개월 무제한 사용권) (x1) 348000.00') THEN '1 Month Unlimited'
    WHEN Charge IN ('MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (835000.00)', 'MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (887000.00)', 'MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (3 MONTH UNLIMITED / 3개월 무제한 사용권 / (15% 혜택) (15% DISCOUNT)) (x1) 0.00', 'MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (3 MONTH UNLIMITED / 3개월 무제한 사용권 / (15% 혜택) (15% DISCOUNT)) (x1) 473500.00', 'MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (3 MONTH UNLIMITED / 3개월 무제한 사용권 / (15% 혜택) (15% DISCOUNT)) (x1) 798300.00', 'MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (3 MONTH UNLIMITED / 3개월 무제한 사용권 / (15% 혜택) (15% DISCOUNT)) (x1) 857000.00', 'MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (3 MONTH UNLIMITED / 3개월 무제한 사용권 / (15% 혜택) (15% DISCOUNT)) (x1) 877000.00', 'MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (3 MONTH UNLIMITED / 3개월 무제한 사용권 / (15% 혜택) (15% DISCOUNT)) (x1) 887000.00') THEN '3 Month Unlimited'
    WHEN Charge = 'MONTHLY UNLIMITED MEMBERSHIPS / 월 무제한 사용권 (6 MONTH UNLIMITED / 6개월 무제한 사용권 / (20% 혜택) (20% DISCOUNT)) (x1) 1670000.00' THEN '6 Month Unlimited'
    WHEN Charge IN ('45일 챌린지 / 45 DAY CHALLENGE', 'Challenge package (Challenge package) (x1) 746000.00', '2WEEK INITIATOR CHALLENGE (2WEEK INITIATOR CHALLENGE) (x1) 0.00') THEN 'Challenge'
    ELSE 'etc' 
  END AS Plan_category 
FROM `F45_KR.GDE_transaction_W4_Oct_2022`

2. [3:3]的含义 #

这是SQL解析器给出的精确错误位置：

• 第一个3代表代码的第3行
• 第二个3代表该行的第3个字符位置
  解析器在这个位置发现了不符合语法的内容（这里是多余的CASE关键字）。

3. 使用WITH子句的用法差异 #

使用WITH子句定义CTE（公共表表达式）时，CASE表达式的语法规则和普通SELECT语句完全一致，不存在语法差异。仅需注意：

• 在CTE内部使用CASE时，同样遵循单个CASE开头、多WHEN串联、ELSE和END收尾的规则
• 多个CTE之间用逗号分隔，最后一个CTE后直接跟主查询语句

示例代码：

WITH filtered_transactions AS (
  SELECT * 
  FROM `F45_KR.GDE_transaction_W4_Oct_2022`
  WHERE Charge IS NOT NULL
)
SELECT *,
  CASE 
    WHEN Charge IN ('Free week extension (0.00)', 'Free week extension (Free