group by ,order by ,having 简单用法

最新推荐文章于 2025-07-03 13:46:19 发布

原创最新推荐文章于 2025-07-03 13:46:19 发布 · 864 阅读

0 ·

CC 4.0 BY-SA版权

plsql开发专栏收录该内容

14 篇文章

订阅专栏

本文介绍了SQL中oraderby、groupby及having子句的基本用法，并通过具体实例展示了如何进行数据排序、分组及聚合列筛选。了解这些操作对于有效管理和分析数据库中的大量信息至关重要。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

orader by：就是排序，默认升序，desc就是降序。

group by：分组，前面必须有聚合函数，分组后面跟的是非聚合列，sql语句循序优先于orader by。

having ：用于聚合列的筛选。

举例说明：

SQL> select tablespace_name,sum(extents),sum(blocks),sum(bytes)/1024/1024 total_MB from dba_segments group by tablespace_name;

TABLESPACE_NAME                                              SUM(EXTENTS) SUM(BLOCKS)   TOTAL_MB
------------------------------------------------------------ ------------ ----------- ----------
SYSAUX                                                               4859       92848    725.375
EAS_D_TEST0319_STANDARD                                             20677     2270920 17741.5625
UNDOTBS1                                                              128        4864         38
EAS_D_ZYWYDB_STANDARD                                               23819     2674256  20892.625
EAS_D_TEST0319_TEMP2                                                  776        6208       48.5
EAS_D_ZYTEST0228_STANDARD                                           25870     2276664 17786.4375
USERS                                                                   6          48       .375
SYSTEM                                                               3461      164544     1285.5
EAS_D_ZYWYDB_TEMP2                                                   7996       63968     499.75
EAS_D_ZYDB_STANDARD                                                 20393     2332464  18222.375
EAS_D_ZYTEST0228_TEMP2                                               5445       43560   340.3125
EAS_D_TEST1201_TEMP2                                                 5820       46560     363.75
EAS_D_ZYDB_TEMP2                                                     7999       63992   499.9375
EAS_D_TEST1201_STANDARD                                             20705     2046832  15990.875
PERFSTAT                                                             2308       63840     498.75

已选择15行。

SQL> select tablespace_name,sum(extents),sum(blocks),sum(bytes)/1024/1024 total_MB from dba_segments order by tablespace_name group by tablespace_name;
select tablespace_name,sum(extents),sum(blocks),sum(bytes)/1024/1024 total_MB from dba_segments order by tablespace_name group by tablespace_name
                                                                                                                         *
第 1 行出现错误:
ORA-00933: SQL command not properly ended


SQL> select tablespace_name,sum(extents),sum(blocks),sum(bytes)/1024/1024 total_MB from dba_segments group by tablespace_name order by tablespace_name;

TABLESPACE_NAME                                              SUM(EXTENTS) SUM(BLOCKS)   TOTAL_MB
------------------------------------------------------------ ------------ ----------- ----------
EAS_D_TEST0319_STANDARD                                             20677     2270920 17741.5625
EAS_D_TEST0319_TEMP2                                                  776        6208       48.5
EAS_D_TEST1201_STANDARD                                             20705     2046832  15990.875
EAS_D_TEST1201_TEMP2                                                 5820       46560     363.75
EAS_D_ZYDB_STANDARD                                                 20393     2332464  18222.375
EAS_D_ZYDB_TEMP2                                                     7999       63992   499.9375
EAS_D_ZYTEST0228_STANDARD                                           25870     2276664 17786.4375
EAS_D_ZYTEST0228_TEMP2                                               5445       43560   340.3125
EAS_D_ZYWYDB_STANDARD                                               23819     2674256  20892.625
EAS_D_ZYWYDB_TEMP2                                                   7996       63968     499.75
PERFSTAT                                                             2308       63840     498.75
SYSAUX                                                               4859       92848    725.375
SYSTEM                                                               3461      164544     1285.5
UNDOTBS1                                                              128        4864         38
USERS                                                                   6          48       .375

已选择15行。

SQL> select tablespace_name,sum(extents),sum(blocks),sum(bytes)/1024/1024 total_MB from dba_segments group by tablespace_name having sum(blocks) > 200000
  2   order by tablespace_name;

TABLESPACE_NAME                                              SUM(EXTENTS) SUM(BLOCKS)   TOTAL_MB
------------------------------------------------------------ ------------ ----------- ----------
EAS_D_TEST0319_STANDARD                                             20677     2270920 17741.5625
EAS_D_TEST1201_STANDARD                                             20705     2046832  15990.875
EAS_D_ZYDB_STANDARD                                                 20393     2332464  18222.375
EAS_D_ZYTEST0228_STANDARD                                           25870     2276664 17786.4375
EAS_D_ZYWYDB_STANDARD                                               23819     2674256  20892.625