SQLite:如何命名“值"中的列子选择 [英] SQLite: how to name columns in a "values" subselect

问题描述

在 postgres 中我可以说:

In postgres I can say:

test=# select * from (values(1),(3),(7)) as foo(id);
 id
----
  1
  3
  7
(3 rows)

这意味着这样的子选择随后可以使用 foo.id 等与其他表连接

This means that such a subselect can subsequently be joined with other tables using foo.id etc.

在 sqlite 中我可以说:

In sqlite I can say:

sqlite> select * from (values(1),(3),(7)) as foo;

----------
1
3
7

但是如果我说 foo(id) 我会得到一个错误:

but if I say foo(id) I'll get an error:

sqlite> select * from (values(1),(3),(7)) as foo (id);
Error: near "(": syntax error

显然，对于通常的子选择(例如 "(select ... as a, ... as b from... ) as foo" )，您可以简单地命名每个字段.

Obviously with usual subselects (e.g. "(select ... as a, ... as b from... ) as foo" ) you could simply name each field.

我发现在这种情况下命名列的唯一简单解决方法是执行以下联合:

The only simple workaround I've found to name columns in such a situation is to do an union like:

sqlite> select * from (select 1 as id where 1=0 union values(1),(3),(7)) foo;
id        
----------
1         
3         
7         

有没有更好的方法来命名 SQLite 中这种子选择"中的列?

Is there a better way to name columns in this kind of "subselects" in SQLite?

推荐答案

由 VALUES 创建的列具有您不想使用的名称(尽管可能):

The columns created by VALUES have names that you would not want to use (although it's possible):

sqlite> .mode columns
sqlite> .header on
sqlite> select * from (values(1,2,3));
            :1          :2        
----------  ----------  ----------
1           2           3         
sqlite> select "", ":1", ":2" from (values(1,2,3));
            :1          :2        
----------  ----------  ----------
1           2           3         

这是无法改变的；没有比在真正的"SELECT 前面加上 UNION ALL 更好的方法了.

This cannot be changed; there is no better way than to prefix a 'real' SELECT with UNION ALL.

这篇关于SQLite:如何命名“值"中的列子选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！