Oracle 11g: The difference between WHERE and HAVING
来源:互联网 发布:期货软件免费下载 编辑:程序博客网 时间:2024/05/23 13:10
SQL> select * from sys.SHIP_CABINS ;SHIP_CABIN_ID SHIP_ID ROOM_ ROOM_STYLE ROOM_TYPE WINDOW GUESTS SQ_FT BALCONY_SQ_FT------------- ---------- ----- ---------- -------------------- ---------- ---------- ---------- ------------- 1 102 Suite Standard Ocean 4 533 2 103 Stateroom Standard Ocean 2 160 3 104 Suite Standard None 4 533 4 105 Stateroom Standard Ocean 3 205 5 106 Suite Standard None 6 586 6 107 Suite Royal Ocean 5 1524 7 108 Stateroom Large None 2 211 8 109 Stateroom Standard None 2 180 9 110 Stateroom Large None 2 225 10 702 Suite Presidential None 5 1142 11 703 Suite Royal Ocean 5 1745 12 704 Suite Skyloft Ocean 8 722
Group by without WHERE and HAVING
SQL> select room_style, room_type, sum(GUESTS) "GUEST SUM" from sys.ship_cabins group by room_style, room_type ;ROOM_STYLE ROOM_TYPE GUEST SUM---------- -------------------- ----------Stateroom Standard 7Suite Standard 14Stateroom Large 4Suite Skyloft 8Suite Royal 10Suite Presidential 5
HAVING
SQL> select room_style, room_type, sum(GUESTS) "GUEST SUM" from sys.ship_cabins group by room_style, room_type having room_type<>'Royal' ;ROOM_STYLE ROOM_TYPE GUEST SUM---------- -------------------- ----------Stateroom Standard 7Suite Standard 14Stateroom Large 4Suite Skyloft 8Suite Presidential 5
WHERE
SQL> select room_style, room_type, sum(GUESTS) "GUEST SUM" from sys.ship_cabins where room_type<>'Royal' group by room_style, room_type ;ROOM_STYLE ROOM_TYPE GUEST SUM---------- -------------------- ----------Stateroom Standard 7Suite Standard 14Stateroom Large 4Suite Skyloft 8Suite Presidential 5
Summary:
There is no difference between them when there is no aggregate function in HAVING clause.
However, we do this in HAVING.
SQL> select room_style, room_type, sum(GUESTS) "GUEST SUM" from sys.ship_cabins group by room_style, room_type having sum(GUESTS)>8 ;ROOM_STYLE ROOM_TYPE GUEST SUM---------- -------------------- ----------Suite Standard 14Suite Royal 10
There is no way to filter out sum(GUESTS)>8 entries using WHERE, because it doesn't allow to use Aggregate function in WHERE clause.
- Oracle 11g: The difference between WHERE and HAVING
- Difference between HAVING and WHERE Clause
- Sql 中having 和where的区别 SQL hardest question What is the difference between the WHERE and HAVING claus
- The main difference between oracle 11g and oracle 12c for SDO
- Difference between the having clause and the group by statement
- the difference between Oracle and Mysql
- The difference between (()) and ().
- What's the difference between gcc and g++/gcc-c++?
- What is the difference between g++ and gcc?
- the difference between "./" and "sh"
- The difference between EXISTS and IN upon Oracle
- the difference between the SOURCELIBS and TARGETLIBS
- The difference between Const and ReadOnly
- The difference between Failure and Success
- The difference between Process and Procedure
- The difference between GetDC and GetWindowDC
- The difference between Chinese and American
- The difference between J2ee and Ruby
- inline-block
- .net添加fckeditor
- 在本地建立SVN服务器的步骤
- shell学习笔记之if参数
- Java异步HTTP请求
- Oracle 11g: The difference between WHERE and HAVING
- Windows下启动SVN服务应注意的七大问题
- 你刚才在淘宝上买了一件东西【技术普及贴】
- WS_BORDER以及WS_EX_CLIENTEDGE的研究
- 有关DM9000驱动问题
- css 和 javascript 后面加上版本号有什么用?
- C++标准IO库
- 【STL】map
- 关于MSN2011登录时800488eb错误的解决办法