group by ,order by ,having 简单用法

本文介绍了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



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值