SQLite3与PostgreSQL的CTE语法差异及报错解决请求

问题背景 #

在Jupyter Notebook中练习SQL：使用sqlite3连接本地数据库时，以下SQL语句可正常通过pd.read_sql(sql, connection)执行；但通过SQLAlchemy创建的engine连接ElephantSQL（PostgreSQL），用pd.read_sql(sql, engine)执行时抛出语法错误。

原SQL语句：

with medium_credits as (
    select t.credit_amount from german_credit t
    where t.credit_amount > 1000 and t.credit_amount < 3000
    group by t.credit_amount),
    
medium_credits_info as (
    select * from german_credit t
    where t.credit_amount in medium_credits)
    

select t.purpose, t.housing, count(1) as count 
from medium_credits_info t
    
group by t.purpose, t.housing

报错信息 #

ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "medium_credits"
LINE 14:     where t.credit_amount in medium_credits)

解决方法 #

PostgreSQL对IN子句的语法要求更严格，必须将CTE/子查询的结果用括号包裹并明确指定查询字段，而SQLite支持简化写法。只需修改medium_credits_info中的IN条件即可：

修改后的SQL语句 #

with medium_credits as (
    select t.credit_amount from german_credit t
    where t.credit_amount > 1000 and t.credit_amount < 3000
    group by t.credit_amount),
    
medium_credits_info as (
    select * from german_credit t
    where t.credit_amount in (select credit_amount from medium_credits))
    

select t.purpose, t.housing, count(1) as count 
from medium_credits_info t
    
group by t.purpose, t.housing

优化写法（可选） #

可以用JOIN替代子查询，逻辑更清晰且效率更高，同时把原group by替换为distinct更贴合"去重获取金额"的需求：

with medium_credits as (
    select distinct t.credit_amount from german_credit t
    where t.credit_amount > 1000 and t.credit_amount < 3000)

select t.purpose, t.housing, count(1) as count 
from german_credit t
join medium_credits mc on t.credit_amount = mc.credit_amount
group by t.purpose, t.housing

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