使用Presto SQL中的WITH AS和JOIN合并

huangapple go评论61阅读模式
英文:

Combine WITH AS and JOIN in Presto SQL

问题

In Presto SQL, you cannot use "WITH table_name AS (... JOIN ...)" directly. This might be specific to Presto SQL. To achieve your desired result, you can try a different approach.

英文:

For some reason, in presto sql, I cannot use "WITH table_name AS (... JOIN ...)". For instance:

WITH table_name AS 

    (SELECT att1 FROM table1

    LEFT OUTER JOIN 
    
    SELECT att2 FROM table2

    ON att1=att2)

SELECT 
    *
FROM
    table_name

Gives syntax error. I've tried many combinations, unsuccessfully. I have no idea if it is common to most sql engines or just presto. Would anyone know how to do it ?

答案1

得分: 1

你的语法有问题。 应该是:

<!-- language: sql -->

WITH table_name AS (
    SELECT t1.att1, t2.att2
    FROM table1 t1
    LEFT JOIN table2 t2
        ON t1.att1 = t2.att2
)

SELECT *
FROM table_name;

一个查询/子查询应该只有一个 SELECT 子句。 此外,我已经添加了查询中的表别名,以便更容易阅读。

英文:

Your syntax is off. It should be:

<!-- language: sql -->

WITH table_name AS (
    SELECT t1.att1, t2.att2
    FROM table1 t1
    LEFT JOIN table2 t2
        ON t1.att1 = t2.att2
)

SELECT *
FROM table_name;

A single query/subquery should have only one SELECT clause. In addition, I have added table aliases to the query to make it easier to read.

huangapple
  • 本文由 发表于 2023年5月11日 00:25:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/76220685.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定