在sqlite3 where子句中使用列别名

问题描述：

我知道在oracle/mysql/sqlserver select 语句中，您不能在 where 子句中引用列别名，因为它是在之前执行的选择，但它在sqlite3中有效:

I know that in oracle/mysql/sqlserver select statement you are not able to reference a column alias in the where clause because it is executed before the select, yet it works in sqlite3:

sqlite> create table t (c1 text, c2 text);
sqlite> insert into t values ("a1", "a2");
sqlite> insert into t values ("b1", "b2");
sqlite> select c1, c2 from t;
a1|a2
b1|b2
sqlite> select c1, c2 as alias_c2 from t where alias_c2='b2';
b1|b2

为什么在sqlite中有这种可能?

Why is this possible in sqlite?

答

使用启用了 SQLITE_DEBUG 标志的sqlite3:

Using sqlite3 with SQLITE_DEBUG flag enabled:

sqlite> create table x (x1 integer);
sqlite> insert into x values (1);
sqlite> insert into x values (2);
sqlite> insert into x values (3);
sqlite> insert into x values (4);
sqlite> insert into x values (5);
sqlite> pragma vdbe_listing=1;
VDBE Program Listing:
   0 Expire           0    0    0      00 
   1 Halt             0    0    0      00 
sqlite> select x1*x1 as s from x where s>-10 and s>-9 and s>0 and s>-4 and s>2;
VDBE Program Listing:
   0 Trace            0    0    0      00 
   1 Integer        -10    1    0      00 
   2 Integer         -9    2    0      00 
   3 Integer          0    3    0      00 
   4 Integer         -4    4    0      00 
   5 Integer          2    5    0      00 
   6 Goto             0   26    0      00 
   7 OpenRead         0    3    0 1    00 x
   8 Rewind           0   24    0      00 
   9 Column           0    0    7      00 x.x1
  10 Multiply         7    7    6      00 
  11 Le               1   23    6      6A 
  12 Multiply         7    7    6      00 
  13 Le               2   23    6      6A 
  14 Multiply         7    7    6      00 
  15 Le               3   23    6      6A 
  16 Multiply         7    7    6      00 
  17 Le               4   23    6      6A 
  18 Multiply         7    7    6      00 
  19 Le               5   23    6      6A 
  20 Column           0    0    6      00 x.x1
  21 Multiply         6    6    9      00 
  22 ResultRow        9    1    0      00 
  23 Next             0    9    0      01 
  24 Close            0    0    0      00 
  25 Halt             0    0    0      00 
  26 Transaction      0    0    0      00 
  27 VerifyCookie     0    4    0      00 
  28 TableLock        0    3    0 x    00 
  29 Goto             0    7    0      00 
s   
----
4   
9   
16  
25  
sqlite>

从上面的指令堆栈可以看出，行的循环(第8-23行)为 Multiply 和 Le 命令code> where 子句，用于表中的每一行.

As can be seen from the instruction stack above, the loop over the rows (lines 8-23) repeats the Multiply and Le commands for each expression in the where clause, for each row in the table.

因此，要回答我自己的问题，sqlite引擎能够通过在 where 的执行时间从 select 替换其定义来使用列别名.

So to answer my own question, sqlite engine is able to use the column aliases by substituting their definitions from the select at execution time of the where.

在sqlite3 where子句中使用列别名

相关推荐