CN - 简体中文
联 系 我 们
售前咨询
产品、解决方案咨询
售后咨询
微信关注：星环科技服务号
更多联系方式 >
分布式时序数据库
9.3
分布式时序数据库
9.3
本页导航
6.4.8 子查询
更新时间：4/9/2022, 9:53:23 AM

子查询是嵌套在查询语句中的查询语句。子查询根据是否和包含它的父查询的结果相关分为非关联子查询和关联子查询。 Timelyre高度支持子查询的各种嵌套：非关联子查询可以在FROM,WHERE,SELECT和HAVING子句中嵌套。 关联子查询可以在WHERE和SELECT中嵌套，而不能在HAVING和FROM子句中嵌套。

非关联子查询：Non-Correlated Subqueries

非关联子查询内容和包含它的父查询结果不相关。当子查询和父查询不相关，Timelyre会在执行父查询之前先执行完成子查询。

在WHERE子句中嵌套

举例：单行单列的子查询结果

下例查询股票交易平台用户信息表中第一个注册的账户的账户持有人，账户号码，持有人身份证，账户级别和注册时间。

SELECT name, acc_num, citizen_id, acc_level, reg_date  
FROM user_info 
WHERE reg_date = (
    SELECT MIN(reg_date) FROM user_info
);
复制
name acc_num citizen_id acc_level reg_date

华*

5224133

42**1

B

2008-02-14

注意，这里子查询SELECT min(reg_date) FROM user_info的结果是单列单行的，所以WHERE子句中的过滤条件可以是一个等式。如果子查询的结果有多列或者多行，过滤条件需要有变化。

举例：单行多列的子查询结果

IN运算符 当子查询结果有不止一条记录，要用IN来表示查询结果须是子查询结果集合中的元素：

下例查询员工信息表中所有有下级员工的员工名字：

SELECT employee_name 
FROM employee_info 
WHERE employee_id IN (
    SELECT sup_id FROM employee_info
);
复制
employee_name

戴**

郑*

吴**

NOT IN运算符

我们也可以查询没有下级员工的员工名字：

SELECT employee_name 
FROM employee_info 
WHERE employee_id NOT IN (
    SELECT sup_id FROM employee_info
);
复制
employee_name

张**

王**

许**

在FROM子句中嵌套

举例

下例查询所有进行过交易的账户持有人名字：

SELECT DISTINCT name 
FROM (
    SELECT name FROM user_info 
    JOIN transactions 
    ON user_info.acc_num = transactions.acc_num
);
复制
name

华*

马**

潘**

管**

邱*

魏**

祝**

李**

下例查询所有个人平均交易额大于所有平均交易额的用户名字

SELECT name 
FROM user_info JOIN (
    SELECT transactions.acc_num, AVG(price*amount) avg_trans 
    FROM transactions 
    GROUP BY transactions.acc_num
) temp 
    ON user_info.acc_num = temp.acc_num 
    WHERE avg_trans > (SELECT AVG(price*amount) FROM transactions);
复制
name

华*

李**

管**

祝**

在SELECT子句中嵌套

举例

下例查看各用户的个人平均交易额和所有交易的平均交易额的差：

SELECT acc_num, AVG(price*amount) - (SELECT AVG(price*amount) FROM transactions) AS avg_gap
FROM transactions 
GROUP BY acc_num;
复制
acc_num avg_gap

3912384

-685.6499999999996

6670192

971.3500000000004

2394923

1126.3500000000004

2755506

4797.35

6513065

-20.316666666666606

0700735

-1364.6499999999996

5224133

1577.3500000000004

6600641

-4062.6499999999996

在HAVING子句中嵌套

举例

下例查询最大一笔交易的执行账户和交易额。

SELECT acc_num, MAX(price*amount) AS max_value
FROM transactions 
GROUP BY acc_num 
HAVING MAX(price*amount) = (
    SELECT max(price*amount) FROM transactions
);
复制
acc_num max_value

6513065

12866.0
关联子查询(Correlated Subquery)

关联子查询的内容和父查询相关。Timelyre会对每条在父查询中出现的记录执行一次子查询。

注意:关联子查询中的关联条件不支持OR,也不支持仅包含非等值比较。

在WHERE子句中嵌套

Timelyre支持的WHERE子句嵌套需要满足以下要求：

  1. WHERE子句中必须包含至少一条等值关系，如果执行业务没有客观要求等值关联，请用户手动添加条件“1=1”。这是为了避免资源被贪婪占用导致枯竭，以保证系统的稳定性。

  2. 主查询和子查询之间必须用标量比较运算符连接（包括‘>’、‘<’、‘=’、‘<>’、‘>=’、‘<=’）。

  3. 要求子查询的结果必须是一行一列的返回，即标量。

  4. 子查询中允许有等值与非等值条件。

举例

下例查询了总共进行过3笔交易的账户持有人姓名和账户号码。

注意，当关联子查询中有COUNT函数时，必须打开开关set hive.support.subquery.join.conversion.count=true。

set hive.support.subquery.join.conversion.count=true;
SELECT user_info.name, user_info.acc_num 
FROM user_info 
WHERE 3=(
    SELECT COUNT(*) FROM transactions 
    WHERE user_info.acc_num = transactions.acc_num
);
复制
name acc_num

邱*

0700735

举例

SELECT COUNT(*) AS cnt FROM TABLEA A, TABLEB B
WHERE ((1=1)) AND ((A.salary > (SELECT (SUM(csA.age) - 114)
                    FROM TABLEA csA, TABLEB csB
                    WHERE (A.age = csA.age) AND
                    ((csB.salary / -9) = (A.salary * -79)))));
复制

EXISTS和NOT EXISTS

语法

SELECT select_expression, select_expression, ...
FROM table_reference
WHERE (EXISTS|NOT EXISTS) (subquery)
复制

在WHERE中嵌套子查询时经常会用到EXISTS和NOT EXISTS。当我们只关心子查询有记录返回，而不关心子查询返回的记录内容和记录条数时，我们就可以用WHERE EXISTS。WHERE EXISTS用来查看子查询中的关系是否成立并且返回使得子查询中关系成立的记录（也就是过滤掉使得子查询中的关系不成立的记录）。比如，假设查询买过单股价格在100元以内的股票的用户，语句应为：

举例

SELECT user_info.name, user_info.acc_num 
FROM user_info WHERE EXISTS (
    SELECT 1 FROM transactions 
    WHERE user_info.acc_num = transactions.acc_num 
    AND price < 100
);
复制
name acc_num

马**

6513065

华*

5224133

潘**

6600641

李**

2755506

管**

2394923

邱*

0700735

祝**

6670192

魏**

3912384

事实上，我们建议如果WHERE子句需要满足某种关系（大于、等于、小于、不等于，等等），尽量使用WHERE EXISTS并在子查询中表达关系，而不是通过比较子查询的结果和别的量来表达关系。 WHERE NOT EXISTS则用来查看子查询中的关系是否成立并且返回使得子查询中关系不成立的记录（也就是过滤掉使得子查询中的关系成立的记录）。

举例

下例查询了所有没有进行交易的账户持有人姓名和账户号码

SELECT user_info.name, user_info.acc_num 
FROM user_info WHERE NOT EXISTS (
    SELECT 1 FROM transactions 
    WHERE user_info.acc_num = transactions.acc_num
);
复制
name acc_num

宁**

4580952

李*

8725869

这里，WHERE NOT EXISTS子句中嵌套的子查询返回的是一个常数，这充分体现了EXISTS和NOT EXISTS仅关心子查询 是否 返回结果，而不关心返回的结果 是什么 。

在SELECT子句中嵌套

举例

下例返回所有账户的持有人姓名，账户号码和账户平均交易额：

SELECT user_info.name, user_info.acc_num, (
    SELECT AVG(price*amount) FROM transactions 
    WHERE user_info.acc_num = transactions.acc_num
) 
FROM  user_info;
复制
name acc_num correlated_subquery_in_alias_0

马**

6513065

4958.333333333333

华*

5224133

6556.0

宁**

4580952

NULL

潘**

6600641

916.0

李**

2755506

9776.0

管**

2394923

6105.0

邱*

0700735

3614.0

李*

8725869

NULL

祝**

6670192

5950.0

魏**

3912384

4293.0
子查询的多层嵌套

Timelyre支持多层嵌套，如:

SELECT select_expression FROM (
    SELECT select_expression FROM (
        SELECT select_expression FROM...
        ...
        )
    )
复制

举例

SELECT name FROM (
                    SELECT name, acc_num FROM (
                        SELECT name, acc_num, password FROM (
                            SELECT name, acc_num, password, bank_acc FROM user_info)
                    )
                   );
复制
name

马**

华*

宁**

潘**

李**

管**

邱*

李*

祝**

魏**
本页导航