DuckDB连接SQLite时含strftime函数的查询返回空结果

• 核心问题：SQL的执行顺序里，WHERE子句的执行早于SELECT子句。你在WHERE里用了SELECT中定义的别名year，这时候year还没被计算出来，相当于在过滤一个不存在的列，自然返回空结果。
• 验证依据：你单独执行包含strftime(CAST(created_at AS DATETIME), '%Y')的SELECT语句时，没有涉及WHERE对别名的引用，所以能正常返回数据。
• 修复方案：
  1. 直接在WHERE子句中使用完整表达式替代别名：

     con.sql("""
     SELECT 
         strftime(CAST(created_at AS DATETIME), '%Y') as year,
         COUNT(*) AS review_count,
         p.title
     FROM reviews r
     JOIN podcasts p ON r.podcast_id = p.podcast_id
     WHERE strftime(CAST(created_at AS DATETIME), '%Y') != 'None'
     GROUP BY year, p.title
     ORDER BY year;
     """)
     

  2. 用CTE先计算出year再操作（可读性更好）：

     con.sql("""
     WITH review_with_year AS (
         SELECT 
             r.*,
             p.title,
             strftime(CAST(r.created_at AS DATETIME), '%Y') as year
         FROM reviews r
         JOIN podcasts p ON r.podcast_id = p.podcast_id
     )
     SELECT 
         year,
         COUNT(*) AS review_count,
         title
     FROM review_with_year
     WHERE year != 'None'
     GROUP BY year, title
     ORDER BY year;
     """)
     

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