如何在SQLite中引用当前作用域？解决列名冲突问题

先看你遇到的冲突场景：

with
  weekdays('name') as (
    values
      ('Monday')
    , ('Tuesday')
    , ('Wednesday')
    , ('Thursday')
    , ('Friday')
    , ('Saturday')
    , ('Sunday')
  )
select
  substr("name",1,3) as "name"
from weekdays
where 1==1
and "name" == 'Monday'
-- 此处想引用SELECT中值为'Mon'的name，但实际会匹配CTE中原始的'Monday'

SQL的执行逻辑是先处理FROM/WHERE子句，再处理SELECT子句，所以WHERE里的name会优先绑定到CTE weekdays的原始列，无法直接引用SELECT中重命名后的同名列。SQLite也没有类似bash中./的作用域引用语法，不过可以通过以下几种方式解决：

方案1：直接使用计算表达式替代别名 #

既然WHERE无法直接调用SELECT的别名，直接把计算逻辑写到WHERE条件里：

with
  weekdays('name') as (
    values
      ('Monday')
    , ('Tuesday')
    , ('Wednesday')
    , ('Thursday')
    , ('Friday')
    , ('Saturday')
    , ('Sunday')
  )
select
  substr("name",1,3) as "name"
from weekdays
where substr("name",1,3) == 'Mon';

方案2：通过子查询隔离作用域 #

把SELECT的结果嵌套到子查询中，外层WHERE就能直接引用子查询的别名：

with
  weekdays('name') as (
    values
      ('Monday')
    , ('Tuesday')
    , ('Wednesday')
    , ('Thursday')
    , ('Friday')
    , ('Saturday')
    , ('Sunday')
  )
select *
from (
  select substr("name",1,3) as "name"
  from weekdays
) as sub_query
where "name" == 'Mon';

方案3：避免同名，使用不同别名 #

从根源上解决冲突，给SELECT中的列起一个不同的名称：

with
  weekdays('name') as (
    values
      ('Monday')
    , ('Tuesday')
    , ('Wednesday')
    , ('Thursday')
    , ('Friday')
    , ('Saturday')
    , ('Sunday')
  )
select
  substr("name",1,3) as "short_name"
from weekdays
where "short_name" == 'Mon';

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