求一个SQL语句（分组后求最大最小值的条数）

Question

TABLE结构
ORG_ID  FEE
A       20
A       30
A       40
A       20
A       60
A       60
B       10
B       30
B       40
B       10
B       60
B       70
B       10
...要的结果是
ORG_ID   分组最小值 最小值的条数 分组最大值 最大值的条数
A        20         2            60         2
B        10         3            70         1求SQL!
注：实际的table很大，百万级记录数。

Answer 1

select a.org_id, b.n1, b.miv, a.n1, a.mav
  from (select a.org_id, m.mav, count(*) n1
          from test a,
               (select a.org_id, max(a.fee) mav from test a group by a.org_id) m
         where a.org_id = m.org_id
           and a.fee = m.mav
         group by a.org_id, m.mav) a,
       (select a.org_id, n.miv, count(*) n1
          from test a,
               (select a.org_id, min(a.fee) miv from test a group by a.org_id) n
         where a.org_id = n.org_id
           and a.fee = n.miv
         group by a.org_id, n.miv) b
 where a.org_id = b.org_id试试这个速度怎样

Answer 2

好像有点慢，结果没跑出来
主要是多次对表table full access了

Answer 3

select org_id, min_fee, (select count(1) from test a where a.org_id = b.org_id and a.fee = b.min_fee), 
 max_fee, (select count(1) from test c where c.org_id = b.org_id and c.fee = b.max_fee)
from 
(
select org_id, min(fee) as min_fee, max(fee) as max_fee 
       from test
   group by org_id 
)b    确定org_id字段上建立了索引

Answer 4

select org_id
    ,sum(decode(flag,1,fee,0)) as fee1
 ,sum(decode(flag,1,rn,0)) as rn1
 ,sum(decode(flag,2,fee,0)) as fee2
 ,sum(decode(flag,2,rn,0)) as rn2
  from(
  select org_id,fee,sum(rn) as rn,1 as flag
  from(
     select org_id,fee
  ,dense_rank()over(partition by org_id order by fee) rn
 from mtest4
 )
  where rn=1
  group by org_id,fee
  union all
  select org_id,fee,sum(rn) as rn,2 as flag
  from(
     select org_id,fee
  ,dense_rank()over(partition by org_id order by fee desc) rn
 from mtest4
 )
  where rn=1
  group by org_id,fee
  )group by org_id
看看这个速度如何？

Answer 5

没有索引的情况下使用这个，就算有索引也可以强制使用全表扫描 /*+full(a)*/
select a.org_id,max(min_fee),count(decode(a.fee,min_fee,1)),max(max_fee),count(decode(a.fee,max_fee,1)) 
from test a,
(select org_id, min(fee) as min_fee, max(fee) as max_fee 
       from test   group by org_id
)b    
where a.org_id=b.org_id
group by a.org_id

Answer 6

如果数据库内存够大的话 ，建议使用hash jion 
select /*+use_hash(a,b)*/ a.org_id,max(min_fee),count(decode(a.fee,min_fee,1)),max(max_fee),count(decode(a.fee,max_fee,1)) 
from test a,
(select org_id, min(fee) as min_fee, max(fee) as max_fee 
       from test   group by org_id
)b    
where a.org_id=b.org_id
group by a.org_id