ITPub博客

首页 > Linux操作系统 > Linux操作系统 > group by 子句学习

group by 子句学习

原创 Linux操作系统 作者:lsq_008 时间:2008-03-11 14:54:52 0 删除 编辑
SQL> alter table demo add (no int);
 
Table altered.
 
SQL> desc demo;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 NAME                                                                       VARCHAR2(30)
 ADDRESS                                                                    VARCHAR2(60)
 PHONE#                                                                     VARCHAR2(12)
 NO                                                                         NUMBER(38)
                                
SQL> update demo set no=rownum;
 
5 rows updated.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from demo;
 
NAME                           ADDRESS                                                      PHONE#               NO
------------------------------ ------------------------------------------------------------ ------------ ----------
10                             asdf                                                         asdf                  1
2                              asa                                                          aaaa                  2
3                              aaa                                                          ffff                  3
2                              bbb                                                          ccc                   4
9                              bbb                                                          ddd                   5
 
SQL> insert into demo select * from demo;
 
5 rows created.
 
SQL> /
 
10 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from demo;
 
NAME                           ADDRESS                                                      PHONE#               NO
------------------------------ ------------------------------------------------------------ ------------ ----------
10                             asdf                                                         asdf                  1
2                              asa                                                          aaaa                  2
3                              aaa                                                          ffff                  3
2                              bbb                                                          ccc                   4
9                              bbb                                                          ddd                   5
10                             asdf                                                         asdf                  1
2                              asa                                                          aaaa                  2
3                              aaa                                                          ffff                  3
2                              bbb                                                          ccc                   4
9                              bbb                                                          ddd                   5
10                             asdf                                                         asdf                  1
 
NAME                           ADDRESS                                                      PHONE#               NO
------------------------------ ------------------------------------------------------------ ------------ ----------
2                              asa                                                          aaaa                  2
3                              aaa                                                          ffff                  3
2                              bbb                                                          ccc                   4
9                              bbb                                                          ddd                   5
10                             asdf                                                         asdf                  1
2                              asa                                                          aaaa                  2
3                              aaa                                                          ffff                  3
2                              bbb                                                          ccc                   4
9                              bbb                                                          ddd                   5
 
20 rows selected.
 
SQL> select count(*) from demo group by no;
 
  COUNT(*)
----------
         4
         4
         4
         4
         4
 
SQL> select no,count(*) from demo group by no;
 
        NO   COUNT(*)
---------- ----------
         1          4
         2          4
         3          4
         4          4
         5          4
 
SQL> delete from demo where rownum<2;
 
1 row deleted.
 
SQL> commit
  2  ;
 
Commit complete.
 
SQL> select no,count(*) from demo group by no;
 
        NO   COUNT(*)
---------- ----------
         1          3
         2          4
         3          4
         4          4
         5          4
 
SQL> select no,count(*) from demo group by no having count(*)>3;
 
        NO   COUNT(*)
---------- ----------
         2          4
         3          4
         4          4
         5          4
 
SQL> select no,count(*) from demo group by no having no>2;
 
        NO   COUNT(*)
---------- ----------
         3          4
         4          4
         5          4
 
SQL> select no,count(*) from demo group by no having no<2;
 
        NO   COUNT(*)
---------- ----------
         1          3
 
SQL>  select no,count(*) from demo group by no having no>1 and no<3;
 
        NO   COUNT(*)
---------- ----------
         2          4
 
SQL>  select no,count(*) from demo group by no having sum(no)>5;
 
        NO   COUNT(*)
---------- ----------
         2          4
         3          4
         4          4
         5          4
 
SQL> select no,count(*) from demo group by no having sum(no)>15;
 
        NO   COUNT(*)
---------- ----------
         4          4
         5          4

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-203932/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 学习索引-1
请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1246145