Good work! Jialin Qiao
Yuan Tian <jackietie...@gmail.com> 于2025年9月5日周五 15:09写道： > > Amazing functionality, congrats. > > We planned to include CTE in 2.0.7 version of IoTDB. > > > Best regards, > ---------------------- > Yuan Tian > > > > On Fri, Sep 5, 2025 at 2:58 PM Colin Shi <shiz...@gmail.com> wrote: > > > Hi All, > > > > CTE functionality is completed and here is the PR ( > > https://github.com/apache/iotdb/pull/16112). > > > > CTEs are particularly useful for breaking down intricate queries into > > smaller, manageable parts, improving maintainability. > > It allows you to define a subquery block within a SQL statement, which can > > be referenced multiple times throughout the query. > > Unlike temporary tables, CTEs are defined within the scope of a single > > statement and do not persist beyond its execution, > > making them a lightweight and efficient tool for query structuring. > > > > The CTE syntax is as below: > > ------------- > > WITH > > * cte_name* [(*col_name* [, *col_name*] ...)] AS (*subquery*) > > [, *cte_name* [(*col_name* [, *col_name*] ...)] AS (*subquery*)] > > --------------- > > Note: we do not support recursive CTE at this moment!! > > > > Here is an example to use CTE: > > --------------- > > with x as (select avg(salary) as avg_salary from employee) select id, name > > from employee where salary > (select avg_salary from x); > > --------------- > > > > Query results from CTEs aren't materialized. Each outer reference to the > > named result set requires the defined query to be re-executed > > (Inline). The next work is the enhancement to support CTE materialization. > > > > > > On Wed, 13 Aug 2025 at 19:37, Yuan Tian <jackietie...@gmail.com> wrote: > > > > > Hi Colin, > > > > > > Glad to see that, we can discuss the feature definition like syntax for > > CTE > > > here. > > > > > > With CTE, I believe some of our complex SQL queries will become more > > > concise and more modular. > > > > > > > > > Best regards, > > > ------------------------ > > > Yuan Tian > > > > > > On Wed, Aug 13, 2025 at 7:12 PM Colin Shi <shiz...@gmail.com> wrote: > > > > > > > Hi all, > > > > > > > > BTW, I'm working CTE feature recently and will update soon... > > > > > > > > >
