/*
分组统计：分组查询，聚合函数统计
注意：
1. 一般与聚合函数配合分组统计
2. select字段、having中字段、order by中字段必须在group by中出现
聚合函数只能出现在select、having、order by
*/
-- 单列分组
/*对性别分组统计*/
select * from t_mc;
select count(10) from t_mc;
select smctag from t_mc group by smctag
select smctag, count(20) from t_mc group by smctag
-- 多列分组
/*对性别年龄分组统计*/
select smctag, count(20) from t_mc group by smctag,nmaxid;
select smctag, nmaxid, count(20) from t_mc group by smctag,nmaxid;
-- 通过having子句限制分组显示结果
select smctag, nmaxid, count(20) from t_mc
group by smctag,nmaxid having count(20) >= 2;
/*
注意：
1. 没有出现在group by中的字段，不能在以下字句中使用
select字段、having中字段、order by中字段
2. 聚合函数只能出现在select、having、order by(不能在where子句中)
*/
-- having与where的使用区别(能先排除的数据要使用where)
select smctag, nmaxid, count(20) from t_mc
where smctag = 1
group by smctag,nmaxid having count(20) >= 2
select smctag, nmaxid, count(20) from t_mc
group by smctag,nmaxid having count(20) >= 2 and smctag = 1
-- 分组统计并排序
select smctag, nmaxid, count(20) from t_mc
where smctag != 2
group by smctag,nmaxid having count(20) >= 2
order by 3 desc;
select nid, smctag, nmaxid, count(20) from t_mc
where smctag = 1 and count(20) >= 2 and nprice!=20000
group by smctag,nmaxid
select smctag, nmaxid, count(20) from t_mc
where smctag = 1
group by smctag,nmaxid having count(20) >= 2 and nprice!=20000
-- 复杂的分组统计,统计不同性别不同年龄人数。使用rollup和cube
select smctag, nmaxid, count(20) from t_mc
group by rollup( smctag,nmaxid)
select smctag, nmaxid, count(20) from t_mc
group by cube( smctag,nmaxid)
-- 分段查询统计(按性别分组并年龄分段统计查询)
select nprice, count(1) from t_mc
group by nprice;
select count(null),count(1) from t_mc;
select count(case when nprice >= 5000 then 1 else null end) "nprice >= 5000",
count(case when nprice < 5000 then 1 else null end),
count(1)
from t_mc;
/* 综合例子 */
-- 统计商品的卖出数量
-- 1. 先要分组统计想要的
select * from t_order_item;
select nmcid, sum(ncount) ncount from t_order_item
group by nmcid;
select m.nid,m.sname, b.ncount from t_mc m
left join (select nmcid, sum(ncount) ncount from t_order_item group by nmcid) b
on m.nid = b.nmcid;
select m.nid, m.sname, nvl(b.ncount, 0) 销售数量 from t_mc m
left join (select nmcid, sum(ncount) ncount from t_order_item group by nmcid) b
on m.nid = b.nmcid;
select m.nid,m.sname, nvl(b.ncount, 0) nc from t_mc m
left join (select nmcid, sum(ncount) ncount from t_order_item group by nmcid) b
on m.nid = b.nmcid
order by 1
select rownum, c.* from
(select m.sname, nvl(b.ncount, 0) nc from t_mc m
left join (select nmcid, sum(ncount) ncount from t_order_item group by nmcid) b
on m.nid = b.nmcid
order by nc desc) c
where rownum <= 5
select * from
(select rownum rn, c.* from (
select m.sname, nvl(b.ncount, 0) nc from t_mc m
left join (select nmcid, sum(ncount) ncount from t_order_item group by nmcid) b
on m.nid = b.nmcid
order by nc desc
) c where rownum <= 10) d
where d.rn > 5
select * from
(select rownum rn, c.* from (
select * from t_mc
) c where rownum <= 10) d
where d.rn > 5
select oi.norderid from t_order_item oi group by oi.nid not exit
