pg学习_子查询

子查询
子查询的语法
SELECT select_list FROM table
WHERE expr operator
(SELECT select_list FROM table);
子查询规则 ()最好放在右边，提高查询的速度。
子查询类型有两种:
单行子查询
多行子查询

1、单行子查询
1)单行子查询所用的操作符号 =
>
>=
< 
<=
<>
简单的单行子查询
highgo=# select * from test
highgo-# where no >(select no from test where name='adam');
组函数的子查询
highgo=# select * from test
highgo-# where no >(select max(no) from test where name='adam');
 no |  name  
----+--------
  3 | eva
  3 | lilith
(2 rows)
having 语句使用子查询
highgo=# select no,count(*) from test group by no
highgo-# having no>(select min(no) from test where name='adam');
 no | count 
----+-------
  3 |     2
  2 |     2
(2 rows)

2、多行子查询
多行子查询所用的操作符号 
in
any 大于> 大于最小
小于< 小于最大
等于= 相对于 in 
all 
大于> 大于最大 
小于< 小于最小
highgo=# select * from test
highgo-# where no in (select no from test where name='adam');
 no |  name  
----+--------
  1 | adam
  2 | lilith
  1 | lilith
  1 | eva
  2 | adam
(5 rows)

highgo=# select * from test
highgo-# where no > any (select no from test where name='adam');
 no |  name  
----+--------
  2 | lilith
  3 | eva
  2 | adam
  3 | lilith
(4 rows)

highgo=# select * from test
highgo-#  where no > all (select no from test where name='adam');
 no |  name  
----+--------
  3 | eva
  3 | lilith
(2 rows)

3、子查询作from子句
在进一步优化后的 HighGo DB 中，当子查询作为 from 的子句时允许不添加别名，这与 oracle 做到了充分的兼容。
highgo=# select * from
highgo-# (select * from test where name='adam')where no=1;
 no | name 
----+------
  1 | adam
(1 row)