当前位置:操作系统 > Unix/Linux >>

[每日一题] OCP1z0-047 :2013-07-22 group by子句

[每日一题] OCP1z0-047 :2013-07-22 group by子句
 

 
这道题就是考where group by having的顺序。。。
答案A不正确:where应该放在group by前面
答案B不正确:having子句是用多行函数(sum,avg,max,min,count)等做为条件
答案C不正确: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 having sum(sal) > 4200 group by job 
 
查询结果是一样的,具体这俩句有区别吗??
 
没有区别,oracle having可以放在前后,常规写法第1种
 
 
三、理解分组
http://www.zzzyk.com/database/201307/229663.html
如何实现比较复杂的分组、小计与合计
 
--测试代码
create table t_dist
(
  TYPE_CD    NUMBER,
  BUYER_ID   VARCHAR2(50),
  ORDER_DT   DATE,
  SO_ID      VARCHAR2(50) not null,
  STOCK_ID   VARCHAR2(50) not null,
  UNIT_PRICE NUMBER,
  DISCOUNT   NUMBER,
  QTY        NUMBER
);
 
 
truncate table t_dist;
insert into t_dist values(1,'CN1001',to_date('2008-04-01','yyyy-mm-dd'),'S9001','29110311',50,10,8);
insert into t_dist values(1,'CN1001',to_date('2008-04-02','yyyy-mm-dd'),'S9002','29110312',60,20,2);
insert into t_dist values(1,'CN1001',to_date('2008-04-03','yyyy-mm-dd'),'S9003','29110313',70,15,3);
insert into t_dist values(2,'CN1001',to_date('2008-04-04','yyyy-mm-dd'),'S9004','29110312',60,15,5);
insert into t_dist values(2,'CN1001',to_date('2008-04-05','yyyy-mm-dd'),'S9005','29110311',70,10,6);
insert into t_dist values(3,'CN1001',to_date('2008-04-06','yyyy-mm-dd'),'S9006','29110313',55,20,4);
insert into t_dist values(3,'CN1001',to_date('2008-04-06','yyyy-mm-dd'),'S9007','29110311',40,10,3);
insert into t_dist values(3,'CN1001',to_date('2008-04-07','yyyy-mm-dd'),'S9008','29110312',50,50,5);
insert into t_dist values(3,'CN1001',to_date('2008-04-07','yyyy-mm-dd'),'S9009','29110313',80,10,2);
insert into t_dist values(1,'CN1001',to_date('2008-04-08','yyyy-mm-dd'),'S9010','29110311',65,10,1);
commit;
 
 
请问:如何实现如下结果,谢谢!
即计算按stock_id,type_cd,distount分组,计算每个产品的销售额(qty*unit_pri
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,