sm_having.txt

来自「orale培训教材包括了所有的sql说明和实例」· 文本 代码 · 共 59 行

DROP TABLE sm_group;

CREATE TABLE sm_group
(FIELD1  VARCHAR2(4),
FIELD2  NUMBER(2),
FIELD3  DATE);

--data insert
INSERT INTO sm_group VALUES('A',2,TO_DATE('1999/09/09','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('A',2,TO_DATE('1999/09/08','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('B',1,TO_DATE('1999/09/07','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('B',2,TO_DATE('1999/09/06','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('C',3,TO_DATE('1999/09/05','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('C',3,TO_DATE('1999/09/04','YYYY/MM/DD'));
--HAVING
select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING sum(FIELD2)>=2;

select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING sum(FIELD2)>=4;



select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING MAX(FIELD1)>'A';
--ok
--HAVING中直接使用字段，（不是分组函数）
select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING FIELD1>'A';
--ok
select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING MAX(FIELD3)>'05_9月_99';
ok
select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING FIELD3>'05_9月_99';
--err!FIELD3不在GROUP BY中。

--SELECT 
select sum(FIELD2),MAX(FIELD3) from sm_group
GROUP BY FIELD1
HAVING FIELD1>'A';

select FIELD1,sum(FIELD2),MAX(FIELD3) from sm_group
GROUP BY FIELD1
HAVING FIELD1>'A';


select sum(FIELD2),FIELD3 from sm_group
GROUP BY FIELD1;
--ERR