sql - vertica sql中的with子句

Question

使用 with 子句查询不同的表/模式

WITH T1 as ( SELECT something as s1 from schema1.table1 ),
WITH T2 as ( SELECT something as s2 from schema2.table1 )
SELECT * FROM T1,T2;    

它给出的错误为：错误：“WITH”处或附近的语法错误

你能指出我在这里缺少什么吗

标签： sqlsubqueryinner-joincommon-table-expressionvertica

Answer 1

with关键字应该只出现一次，在查询的开头：

WITH 
    T1 as ( SELECT something as s1 from schema1.table1 ),
    T2 as ( SELECT something as s2 from schema2.table1 )
SELECT T1.something something1, T2.something something2
FROM T1
CROSS JOIN T2;

请注意，我将您的隐式连接重写为显式cross join. 您还应该为结果集中的列名设置别名，因为两个查询都会生成一个具有相同名称的列。