执行SQL语句时触发‘near "Type"’语法错误，请求排查解决

错误原因 #

1. 列别名格式错误：你给CASE表达式的别名Purchase Type包含空格，SQL解析器会把Purchase当成合法别名，后面的Type就成了无意义的语法片段，直接触发syntax error。
2. WHERE子句执行顺序限制：SQL执行逻辑是先处理WHERE过滤，再执行SELECT里的字段定义，因此WHERE里不能直接引用SELECT阶段才定义的别名。

修正后的代码方案 #

方案1：用引号包裹别名，WHERE子句重复CASE逻辑

SELECT
    InvoiceDate,
    BillingAddress,
    BillingCity,
    total,
    CASE
        WHEN total < 2.00 THEN 'Baseline Purchase'
        ELSE 'Top Performer'
    END AS "Purchase Type" -- 用双引号包裹带空格的别名
FROM
    Invoice
WHERE
    CASE -- 重复CASE逻辑实现过滤
        WHEN total < 2.00 THEN 'Baseline Purchase'
        ELSE 'Top Performer'
    END = 'Top Performer'
ORDER BY
    BillingCity

方案2：用子查询/CTE，允许WHERE引用别名

-- 子查询写法
SELECT *
FROM (
    SELECT
        InvoiceDate,
        BillingAddress,
        BillingCity,
        total,
        CASE
            WHEN total < 2.00 THEN 'Baseline Purchase'
            ELSE 'Top Performer'
        END AS "Purchase Type"
    FROM Invoice
) AS sub_query
WHERE "Purchase Type" = 'Top Performer'
ORDER BY BillingCity

补充说明 #

不同数据库对带空格别名的处理略有差异：

• SQLite/PostgreSQL用双引号"Purchase Type"
• MySQL可以用反引号`Purchase Type`
• SQL Server用方括号[Purchase Type]

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