pg学习_子查询

子查询子查询的语法SELECT select_list FROM tableWHERE expr operator(SELECT select_list FROM table);子查询规则 ()最好放在右边，提高查询的速度。子查询类型有两种:单行子查询多行子查询1、单行子查询1)单行子查询所用的操作符号 =>>=< <=<>简单的单行子查询highgo=# select * from testhighgo-# where no >(select no from test where name='adam');组函数的子查询highgo=# select * from testhighgo-# 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 nohighgo-# having no>(select min(no) from test where name='adam'); no | count ----+-------  3 |     2  2 |     2(2 rows)2、多行子查询多行子查询所用的操作符号 inany 大于> 大于最小小于< 小于最大等于= 相对于 in all 大于> 大于最大 小于< 小于最小highgo=# select * from testhighgo-# 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 testhighgo-# 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 testhighgo-#  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 * fromhighgo-# (select * from test where name='adam')where no=1; no | name ----+------  1 | adam(1 row)