create database test;
\c test;
create table tt (ty varchar(1), num int, abstract varchar(16));
insert into tt values('a', 5, 'a2002');
insert into tt values('a', 2, 'a2001');
insert into tt values('b', 10, 'b2003');
insert into tt values('b', 6, 'b2002');
insert into tt values('b', 3, 'b2001');
insert into tt values('c', 9, 'b2005');
insert into tt values('c', 9, 'b2004');
insert into tt values('c', 8, 'b2003');
insert into tt values('c', 7, 'b2002');
insert into tt values('c', 4, 'b2001');
insert into tt values('a', 11, 'b2001');
test=# select * from tt;
ty | num | abstract
----+-----+----------
a | 5 | a2002
a | 2 | a2001
b | 10 | b2003
b | 6 | b2002
b | 3 | b2001
c | 9 | b2005
c | 9 | b2004
c | 8 | b2003
c | 7 | b2002
c | 4 | b2001
a | 11 | b2001
(11 rows)
test=# select * from tt where num>8;
ty | num | abstract
----+-----+----------
b | 10 | b2003
c | 9 | b2005
c | 9 | b2004
a | 11 | b2001
(4 rows)
test=# select ty, sum(num) from tt group by ty having sum(num)>10;
ty | sum
----+-----
c | 37
b | 19
a | 18
(3 rows)
test=# select ty, sum(num) from tt where num>8 group by ty having sum(num)>10;
ty | sum
----+-----
a | 11
c | 18
(2 rows)
group by 已经给结果数据按照ty字段分类,而另外两个字段比如num,在一条ty下面就会有很多条不同num的数据,于是要用
sun、max等取和、取最大值,把多条不同的num变成一条显示出来。
阅读(892) | 评论(0) | 转发(0) |