[每日一题] OCP1z0-047 :2013-07-22 group by子句
这道题就是考where group by having的顺序。。。
答案Ａ不正确：where应该放在group by前面
答案Ｂ不正确：having子句是用多行函数（sum，avg,max,min,count）等做为条件
答案Ｃ不正确：where应该放在group by前面
参考如下：（其实having 也可以放在group by前面）
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
答案：D
**********************************************************
一、group by分组注意:
drop table t;
drop table t1;
create table t(id number,dt date);
create table t1(id number,dt date);
insert into t values(1,sysdate);
insert into t1 values(1,sysdate+1);
insert into t1 values(1,sysdate+2);
commit;
--表中不正确
select t.id,max(t1.dt) mdt
from t,t1 where t.id=t1.id;
--dual结合select 常量，始终返回一行，不管dual有多少行，所以正确
--在一般表里会出错，这个世界的规则有普通规则和特殊规则，如果都去遵守普遍规则，那么将会世界太平，特殊规则不用刻意去追求和遵循
with t as
(select 1 id,sysdate dt from dual),
t1 as
(select 1 id,sysdate+1 dt from dual union all
select 1,sysdate+2 from dual)
select t.id,max(t1.dt) mdt
from t,t1 where t.id=t1.id;
--t1.id是多行，不行
with t as
(select 1 id,sysdate dt from dual),
t1 as
(select 1 id,sysdate+1 dt from dual
UNION ALL
select 1,sysdate+2 from dual)
select t1.id,max(t1.dt) mdt
from t,t1 where t.id=t1.id;
--让tm来源于表，照样出错，和dual构造都有关系
with tm as
(select 1 id,sysdate dt from t),
t1 as
(select 1 id,sysdate+1 dt from dual union all
select 1,sysdate+2 from dual)
select t1.id,max(t1.dt) mdt
from tm,t1 where tm.id=t1.id;
select dummy from dual;
--错误
select dummy from dual having count(*)=1;
--错误
with t as
(select dummy from dual)
select dummy from t having count(*)=1;
--用dummy就算取别名也出错，dummy列里不是常量？？因为dummy允许多行
with t as
(select dummy x from dual)
select x from t having count(*)=1;
--正确，不用dummy，因为select 常量 from dual;不管dual有多少行，始终返回一行
with t as
(select 'X' x from dual)
select x from t having count(*)=1;
select * from dual;
--给dual插入一条数据
insert into sys.dual values('Y');
----------------------------------------------------神奇的dual------------------------------------------------------------------
SQL> select dummy from dual;
DUMMY
-----
X
Y
SQL> select 'X' x from dual;
X
-
X
SQL> drop table m;
Table dropped
SQL> create table m(d varchar2(10));
Table created
SQL> insert into m values('a');
1 row inserted
SQL> insert into m values('b');
1 row inserted
SQL> select 'X' x from m;
X
-
X
X
二、having写在group by前后都一样
drop table t;
create table t(id number,name varchar2(10),sal number);
insert into t values(1,'a',2000);
insert into t values(1,'b',3000);
insert into t values(2,'c',1000);
insert into t values(2,'x',2000);
insert into t values(3,'d',5000);
insert into t values(4,'e',4000);
commit;
delete from t where id=1;
--下面的结果是一样的，但是最好用第2种，可读性强
select id,max(sal),count(*) from t having count(*)>1 group by id;
select id,max(sal),count(*) from t group by id having count(*)>1;
俩个sql有 什么区别？
1. select job,sum(sal) from emp t group by job having sum(sal) > 4200
2. select job,sum(sal) from emp t havin