testbook

子查询：Subqueries

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

非同步子查询：Non-Correlated Subqueries

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

在WHERE子句中嵌套：
例1：单行单列的子查询结果
下例查询股票交易平台用户信息表中第一个注册的账户的账户持有人，账户号码，持有人身份证，账户级别和注册时间。

[$host] transwarp> SELECT name, acc_num, citizen_id, acc_level, reg_date  FROM user_info WHERE reg_date = (SELECT min(reg_date) FROM user_info);
华微    5224133 420529198911075631      B       20080214

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

例2：单行多列的子查询结果
当子查询结果有不止一条记录，要用IN来表示查询结果须是子查询结果集合中的元素：
IN运算符
下例查询员工信息表中所有有下级员工的员工名字：

[$host] transwarp> SELECT employee_name FROM employee_info WHERE employee_id IN (SELECT sup_id FROM employee_info);
戴李仁
郑希
吴思翼

NOT IN运算符
我们也可以查询没有下级员工的员工名字：

[$host] transwarp> SELECT employee_name FROM employee_info WHERE employee_id NOT IN (SELECT sup_id FROM employee_info);
张鑫叶
王明哲
许玮伦

在FROM子句中嵌套
举例
下例查询所有进行过交易的账户持有人名字：

[$host] transwarp> SELECT DISTINCT name FROM (SELECT name FROM user_info JOIN transactions ON user_info.acc_num = transactions.acc_num);
邱坤
管淑艳
李韩瑶
潘营泽
祝韩恒
魏向卉
马从筠
华微

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

[$host] transwarp> 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);
祝韩恒
华微
李韩瑶
管淑艳

在SELECT子句中嵌套
举例
下例查看各用户的个人平均交易额和所有交易的平均交易额的差：

[$host] transwarp> SELECT acc_num, avg(price*amount) - (SELECT avg(price*amount) FROM transactions) FROM transactions GROUP BY acc_num;
2394923 1126.3500000000004
2755506 4797.35
6513065 -20.316666666666606
3912384 -685.6499999999996
0700735 -1364.6499999999996
6600641 -4062.6499999999996
5224133 1577.3500000000004
6670192 971.3500000000004

在HAVING子句中嵌套
举例
下例查询最大一笔交易的执行账户和交易额。

[$host] transwarp> SELECT acc_num, max(price*amount) FROM transactions GROUP BY acc_num HAVING max(price*amount) = (SELECT max(price*amount) FROM transactions);
6513065 12866.0

同步子查询(Correlated Subquery)

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

在WHERE子句中嵌套
举例
下例查询了总共进行过3笔交易的账户持有人姓名和账户号码。

[$host] transwarp> 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);
邱坤    0700735

EXISTS和NOT EXISTS
语法

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

在WHERE中嵌套子查询时经常会用到EXISTS和NOT EXISTS。当我们只关心子查询有记录返回，而不关心子查询返回的记录内容和记录条数时，我们就可以用WHERE EXISTS。WHERE EXISTS用来查看子查询中的关系是否成立并且返回使得子查询中关系成立的记录（也就是过滤掉使得子查询中的关系不成立的记录）。比如，上面的查询语句可以用WHERE EXISTS重写为：
举例

[$host] transwarp> SELECT user_info.name, user_info.acc_num FROM user_info WHERE EXISTS (SELECT count(\*) cnt FROM transactions WHERE user_info.acc_num = transactions.acc_num HAVING cnt=3);
邱坤    0700735

事实上，我们建议如果WHERE子句需要满足某种关系（大于、等于、小于、不等于，等等），尽量使用WHERE EXISTS并在子查询中表达关系，而不是通过比较子查询的结果和别的量来表达关系。
WHERE NOT EXISTS则用来查看子查询中的关系是否成立并且返回使得子查询中关系不成立的记录（也就是过滤掉使得子查询中的关系成立的记录）。
举例
下例查询了所有没有进行交易的账户持有人姓名和账户号码

[$host] transwarp> 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);
宁新瑶  4580952
李平    8725869

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

在SELECT子句中嵌套
举例
下例返回所有账户的持有人姓名，账户号码和账户平均交易额：

[$host] transwarp> 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;
马从筠  6513065 4958.333333333333
祝韩恒  6670192 5950.0
华微    5224133 6556.0
魏向卉  3912384 4293.0
宁新瑶  4580952 NULL
邱坤    0700735 3614.0
李平    8725869 NULL
潘营泽  6600641 916.0
李韩瑶  2755506 9776.0
管淑艳  2394923 6105.0

子查询的多层嵌套

Inceptor支持多层嵌套，如:

SELECT select_expression FROM (
    SELECT select_expression FROM (
        SELECT select_expression FROM...
        ...
        )
    )

举例

[$host] transwarp> SELECT name FROM (
                    SELECT name, acc_num FROM (
                        SELECT name, acc_num, password FROM (
                            SELECT name, acc_num, password, bank_acc FROM user_info)
                    )
                   );
马从筠
祝韩恒
华微
魏向卉
宁新瑶
邱坤
李平
潘营泽
李韩瑶
管淑艳