如何在' from '别名上连接表?

Question

我怎么能这样做呢?

select * from  (generating table) alias_table
left join (select * from alias_table where id>5) alias_table_2
on alias_table.id=alias_table_2.id

Answer 1

如果你的MySQL版本不超过8，那么你可以使用下面的通用表表达式:

WITH alias_table (column_list from generated table) AS (
generating table
) 
select * from  alias_table
left join (select * from alias_table where id>5) alias_table_2
on alias_table.id=alias_table_2.id

在下面的链接中，你会发现一个工作的例子:https://www.db-fiddle.com/和togetherjs = 2 gjbwjnocb