ORACLE 007 --5 group by 的用法及增强、高级子查询
在介绍GROUP BY 和 HAVING 子句前，我们必需先讲讲sql语言中一种特殊的函数：聚合函数，例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。
SELECT SUM(population) FROM bbc
这里的SUM作用在所有返回记录的population字段上，结果就是该查询只返回一个结果，即所有国家的总人口数。
通过使用GROUP BY 子句，可以让SUM 和 COUNT 这些函数对属于一组的数据起作用。当你指定 GROUP BY region 时，属于同一个region（地区）的一组数据将只能返回一行值，也就是说，表中所有除region（地区）外的字段，只能通过 SUM, COUNT等聚合函数运算后返回一个值。
HAVING子句可以让我们筛选成组后的各组数据，WHERE子句在聚合前先筛选记录．也就是说作用在GROUP BY 子句和HAVING子句前．
而 HAVING子句在聚合后对组记录进行筛选。
让我们还是通过具体的实例来理解GROUP BY 和 HAVING 子句，还采用第三节介绍的bbc表。
SQL实例：
一、显示每个地区的总人口数和总面积：
SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中的不同字段（一或多条记录）作运算。
二、 显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。
SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000
在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。
相反，HAVING子句可以让我们筛选成组后的各组数据．
group by 的增强、高级子查询、DML和DDL语句扩展
组函数
avg,sum,count,min,max
select id,sum(age) from users group by id having sum(age)>40;
超级聚合
rollup,cube
rollup从右往左再聚合
select id,num,sum(age) from users group by rollup(id,num)
cube从左往右取
select id,num,sum(age) from users group by cube(id,num)
grouping函数,返回的值，1肯定，0否定
select id,sum(age),grouping(id) from users;
grouping sets
分别求统计，再union
select id,num,sum(age) from users group by grouping sets((id),(num))
select id,num,sum(age) from users group by rollup((id,num));
select id,num,sum(age) from users group by id,rollup(num);
高级子查询
inner query ,outer query
select name from users where age >(select age from users where id =322);
成对的进行比较
select student_id,teacher_id,class_id from students
where (teacher_id,class_id) in (select teacher_id,class_id from students where student_id in (222,332))
在from中使用子查询,注意要加上别名
select a.id,a.name,b.type from students a,(select type from teachers) b where a.teacher_id=b.id;
标量子查询
精确的从一行中返回一列,独立的
关联子查询
循环的方式加以执行，内查询依赖于外查询
select name,age from users a
where age >(select avg(age) from users b where b.id=a.id )
exists,not exists
select * from students t1 where exists (select * from student_details t2 where t1.id=t2.id and t2.detail_id=22);
关联更新
用另外一张表更新当前表
update test1 t2 set age= (select age from test2 t2 where t1.id=t2.id)
关联删除
delete from test1 t1 where id in (select id from test2 t2 where t1.id=t2.id and age >20))
分层结构
select id,name from student start with id=292 connect by prior id=c_id;
select level,id,name from student start with id=292 connect by prior id=c_id;
DML和DDL语句扩展
DML:INSERT,DELETE,UPDATE
多表插入
无条件insert
有条件all insert
有条件first insert
选择insert
insert [all] [conditional_insert_clause]
[insert_into_clause values_clause] (subquery)
insert all into test1(id,name) values(id,name) into test2(id,address) values(id,address)
select id,name,address from test3 where id>23;
有条件的插入
insert all
when name like 't%' then
into test1(id,name) values(id,name)
when name like 'h%' then
into test2(id,address) values(id,address)
select id,name,address from test3 where id>23;
旋转插入
假设表sales_source_date的表结构为,employee_id,week_id,sales_mon,sales_tue,sales_wed,sales_thu,sales_fri
我想把这些记录保存在sales_info表中，表结构为employee_id,week,sales
insert all
into sales_info values (employee_id,week_id,sales_mon)
into sales_info values (employee_id,week_id,sales_tue)
into sales_info values (employee_id,week_id,sales_wed)
into sales_info values (employee_id,week_id,sales_thu)
into sales_info values (employee_id,week_id,sales_fri)
select employee_id,week_id,sales_mon,sales_tue,sales_wed,sales_thu,sales_fri from sales_source_date;
创建表时创建索引
create table test_index_table
(id int not null primary key using index(create index index1 on test_index_table(id)),
问题：
create table main(id varchar2(20),yearly varchar2(4),unitid varchar2(20));
alter table main add constraint pk_main primary key (id) ;
insert into MAIN (ID, YEARLY, UNITID)
values ('1', '2007', '101');
insert into MAIN (ID, YEARLY, UNITID)
values ('2', '2007', '102');
insert into MAIN (ID, YEARLY, UNITID)
values ('3', '2007', '103');
insert into MAIN (ID, YEARLY, UNITID)
values ('4', '2007', '105');
commit;
create table detail(id varchar2(20),medid varchar2(10),medname varchar2(20),jan number,
feb number,mar number,apr number,may number,jun number,jul number,aug number,sep number,oct number,nov number,dec number);
alter table detail add constraint pk_detail primary key (id,medid) ;
insert into DETAIL (ID, MEDID, MEDNAME, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
values ('1', '001', '止痛片', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1);
insert into DETAIL (ID, MEDID, MEDNAME, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
values ('1', '002', '感冒灵', 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2);
insert into DETAIL (ID, MEDID, MEDNAME, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
values ('2', '001', '止痛片', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1);
insert into DETAIL (ID, MEDID, MEDNAME, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
values ('2', '002', '感冒灵', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
insert into DETAIL (ID, MEDID, MEDNAME, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
values ('2', '003', '伤风胶囊', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
insert into DETAIL (ID, MEDID, MEDNAME, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
values ('3', '002', '感冒灵', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
insert into DETAIL (ID, MEDID, MEDNAME, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
values ('3', '001', '止痛片', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
insert into DETAIL (ID, MEDID, MEDNAME, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
values ('3', '004', '克咳胶囊', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
insert into DETAIL (ID, MEDID, MEDNAME, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
values ('4', '004', '克咳胶囊', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
commit;
我要的结果是这样的，如想要第一个月的统计数
001 止痛片 3
101 1
102 1
103 1
002 感冒灵 4
101 2
102 1
103 1
003 伤风胶囊 1
102 1
004 克咳胶囊 2
103 1
105 1
结果：
SELECT COL1,COL2,COL3 FROM
(
select
MEDID AS COL1
,MEDNAME AS COL2
,SUM(JAN) AS COL3
,MEDID AS COL4
from detail
GROUP BY MEDID,MEDNAME
UNION
SELECT
B.UNITID AS COL1
,CAST(A.JAN AS VARCHAR2(20)) AS COL2
,NULL AS COL3
,A.MEDID AS COL4
FROM DETAIL A
LEFT JOIN MAIN B ON A.ID=B.ID
ORDER BY COL4 ,COL1
)
