postgresql-表的左连接、聚集函数、group by

声明：本PostgreSQl实用指南系列为刘兴(http://deepfuture.iteye.com/)原创，未经笔者授权，任何人和机构不能转载

连接

mydb=# select * from student,citys where student.city=citys.id;
??? name??? | age | city | name | id
------------+-----+------+------+----
?deepfuture |? 20 |??? 1 | 长沙 |? 1
?张三?????? |? 21 |??? 1 | 长沙 |? 1
?未来?????? |? 20 |??? 2 | 湛江 |? 2
(3 rows)

mydb=# select student.name as name,student.age as age,citys.name as city from st
udent,citys where student.city=citys.id;
??? name??? | age | city
------------+-----+------
?deepfuture |? 20 | 长沙
?张三?????? |? 21 | 长沙
?未来?????? |? 20 | 湛江
(3 rows)

左连接
mydb=# select * from citys left outer join student on (student.city=citys.id)
mydb-# ;
?name | id |??? name??? | age | city
------+----+------------+-----+------
?长沙 |? 1 | deepfuture |? 20 |??? 1
?长沙 |? 1 | 张三?????? |? 21 |??? 1
?湛江 |? 2 | 未来?????? |? 20 |??? 2
?北京 |? 3 |??????????? |???? |
(4 rows)

?聚集函数:

?

mydb=# select citys.name,count(*) as 人数 from citys,student where student.city=
citys.id group by citys.name;
?name | 人数
------+------
?长沙 |??? 2
?湛江 |??? 1
(2 rows)

mydb=#