后浪云impala教程-with子句-impala (后浪云服务器)

如果查询太复杂，我们可以为复杂部分定义别名，并使用Impala的with子句将它们包含在查询中。

语法

以下是Impala中的with子句的语法。

with x as (select 1), y as (select 2) (select * From x uNIOn y);

例

假设我们在数据库my_db中有一个名为customers的表，其内容如下 –

[quickstart.cloudera:21000] > select * from customers;Query: select * from customers+----+----------+-----+-----------+--------+| id | name| age | address| salary |+----+----------+-----+-----------+--------+| 1| Ramesh| 32| Ahmedabad | 20000|| 9| robert| 23| banglore| 28000|| 2| Khilan| 25| Delhi| 15000|| 4| Chaitali | 25| Mumbai| 35000|| 7| ram| 25| chennai| 23000|| 6| Komal| 22| MP| 32000|| 8| ram| 22| vizag| 31000|| 5| Hardik| 27| Bhopal| 40000|| 3| kaushik| 23| Kota| 30000|+----+----------+-----+-----------+--------+Fetched 9 row(s) in 0.59s

同样，假设我们有另一个名为employee的表，其内容如下 –

[quickstart.cloudera:21000] > select * from employee;Query: select * from employee+----+---------+-----+---------+--------+| id | name| age | address | salary |+----+---------+-----+---------+--------+| 3| mahesh| 54| Chennai | 55000|| 2| ramesh| 44| Chennai | 50000|| 4| Rupesh| 64| Delhi| 60000|| 1| subhash | 34| Delhi| 40000|+----+---------+-----+---------+--------+Fetched 4 row(s) in 0.59s

以下是Impala中的with子句的示例。 在本示例中，我们使用with子句显示年龄大于25的员工和客户的记录。

[quickstart.cloudera:21000] >with t1 as (select * from customers where age>25),t2 as (select * from employee where age>25)(select * from t1 union select * from t2);

执行时，上述查询给出以下输出。

Query: with t1 as (select * from customers where age>25), t2 as (select * from employee where age>25)(select * from t1 union select * from t2)+----+---------+-----+-----------+--------+| id | name| age | address| salary |+----+---------+-----+-----------+--------+| 3| mahesh| 54| Chennai| 55000|| 1| subhash | 34| Delhi| 40000|| 2| ramesh| 44| Chennai| 50000|| 5| Hardik| 27| Bhopal| 40000|| 4| Rupesh| 64| Delhi| 60000|| 1| Ramesh| 32| Ahmedabad | 20000|+----+---------+-----+-----------+--------+Fetched 6 row(s) in 1.73s