Phoenix现在支持在WHERE 和FROM 中使用子查询。子查询可以被指定在很多地方，比如 IN/NOT IN, EXISTS/NOTEXISTS等。

Subqueries with INor NOT IN

与传统数据库一样，例如：

SELECT ItemName

FROM Items

WHERE ItemID IN

(SELECT ItemID

FROM Orders

WHERE Date >= to_date('2013-09-02'));

返回结果为：

+-------------------+

| ITEMNAME |

+-------------------+

| MU947    |

| MU3508   |

| XT2217    |

+-------------------+

注：我们在之前的博客http://blog.csdn.net/jiangshouzhuang/article/details/52423898中创建Orders表时，Date字段为Char类型，这里需要重新创建表并导入数据，如下：

drop table Orders;

create table IF NOT EXISTS Orders (

OrderID Integer,

CustomerID Char(4),

ItemID Char(4),

Quantity Integer,

Date Date

constraint pk Primary key(OrderID)

);

重新导入数据：

psql.py gpmaster:2181:/hbaseforkylin Orders.sql Orders.csv

Subqueries withEXISTS or NOT EXISTS

我们有可以使用exists实现上面的查询：

SELECT ItemName

FROM Items i

WHERE EXISTS

(SELECT *

FROM Orders

WHERE Date>= to_date('2013-09-02')

AND ItemID = i.ItemID);

结果为：

+-------------------+

| ITEMNAME |

+-------------------+

| MU947    |

| MU3508   |

| XT2217    |

+-------------------+

Semi-joins andAnti-joins

IN/Not IN或EXISTS/NOT EXISTS可以使用Semi-joins和Anti-joins实现。

Semi-join多在子查询exists中使用，对外部数据源的每个键值，查找到内部数据源匹配的第一个键值后就返回，如果找到就不用再查找内部数据源其他的键值了。IN或EXISTS可以转化为Semi-joins。

Anti-join多用于!= not in等查询；如果找到满足条件（!= not in）的不返回，不满足条件（!= not in）的返回，和join相反。NOT IN或NOT EXISTS可以转化为Anti-joins。

Subqueries withComparison Operators

子查询可以使用比较操作符(=, < >, >, > =, <, ! >, !<, or < =)。

比如下面的示例：

SELECT ID, Name

FROM Contest

WHERE Score >

(SELECT avg(Score)

FROM Contest)

ORDER BY Score DESC;

这里子查询只能返回单行，如果返回多行会出现SQL错误提示。

Subqueries withANY/SOME/ALL Comparison Operators

示例：

SELECT OrderID

FROM Orders

WHERE quantity >= ANY

    (SELECT max(quantity)

     FROM Orders

     GROUP BY ItemID);

返回结果：

+----------------+

| ORDERID |

+----------------+

| 1630781 |

| 1630782 |

| 1630783 |

| 1630784 |

| 1630785 |

+----------------+

CorrelatedSubqueries

与传统数据一样，子查询包含的内容与外层查询相关。

SELECT PatentID, Title

FROM Patents p

WHERE FileDate <= ALL

    (SELECT FileDate

     FROM Patents

     WHERE Region = p.Region);

Phoenix会优化上面的查询，通过重写为等式join查询，以便内部查询仅仅需要执行一次，上面的相关性子查询被Phoenix重写为：

SELECT PatentID, Title

FROM Patents p

JOIN

    (SELECT Region col1, collect_distinct(FileDate) col2

     FROM Patent

     GROUP BY Region) t1

ON Region = t1.col1

WHERE FileDate <= ALL(t1.col2);

collect_distinct为Phoenix内部函数，收集一列或表达式所有不同的值到一个Array中。

AND/OR Branches andMultiple levels of Nesting

嵌套子查询，可以在Where，And或Or中使用子查询。

SELECT ItemID, ItemName

FROM Items i

WHERE NOT EXISTS

(SELECT *

FROM Orders

WHERE CustomerID IN

(SELECT CustomerID

FROM Customers

WHERE Country = 'Belgium')

AND Quantity< 1000

AND ItemID = i.ItemID)

OR ItemID != ALL

(SELECT ItemID

FROM Orders

WHERE CustomerID IN

(SELECT CustomerID

FROM Customers

WHERE Country = 'Germany')

AND Quantity < 2000);

Row subqueries

一个子查询可以返回一行的多个字段。

SELECT CUSTOMERID, COUNTRY

FROM Customers2

WHERE (CUSTOMERID, COUNTRY) IN

(SELECT CUSTOMERID, COUNTRY

FROM Customers

WHERE CUSTOMERNAME = 'Logica');

Derived Tables

在From中指定的子查询也被称为“derived table”。

SELECT m, count(*)

FROM

(SELECT max(QUANTITY) m

FROM Orders

GROUP BY ORDERID) AS t

GROUP BY m

ORDER BY count(*) DESC;

Derived table可以在join查询中作为join表指定在任何地方。

 

顶