[简单]oracle group by问题解决记录

bopgbvincw

　　       同事写了个SQL，发现group by之后总数不对，不用group by查询有26条数据，使用group by查询总数只有16条数据，同事写的sql类似如下：
　　       

select t.sec_id,t.type_t,t.prod_id,t.amt_num
from tmp_t t
group by t.sec_id,t.type_t,t.prod_id,t.amt_num
order by t.sec_id,t.type_t,t.prod_id,t.amt_num
　　看了之后跟同事说group by 之后，amt_num要使用sum,同事一直不相信，只好写个简单的例子，上例子：
　　   

with tmp_t as(
select '0' as sec_id,'10000001' as type_t,'80008510' as prod_id,1 as amt_num from dual union all
select '0','10000001','80008510',1 from dual union all
select '0','10000001','80008510',1 from dual union all
select '0','10000001','80008510',1 from dual union all
select '0','10000002','80008510',1 from dual union all
select '0','10000002','80008510',1 from dual)
　　如上所示，只有2条数据，其他的是重复的。
　　

  with tmp_t as(
select '0' as sec_id,'10000001' as type_t,'80008510' as prod_id,1 as amt_num from dual union all
select '0','10000001','80008510',1 from dual union all
select '0','10000001','80008510',1 from dual union all
select '0','10000001','80008510',1 from dual union all
select '0','10000002','80008510',1 from dual union all
select '0','10000002','80008510',1 from dual)  
select *
from tmp_t t
order by t.sec_id, t.type_t, t.prod_id, t.amt_num
　　

   group by使用sum:
　　

  with tmp_t as(
select '0' as sec_id,'10000001' as type_t,'80008510' as prod_id,1 as amt_num from dual union all
select '0','10000001','80008510',1 from dual union all
select '0','10000001','80008510',1 from dual union all
select '0','10000001','80008510',1 from dual union all
select '0','10000002','80008510',1 from dual union all
select '0','10000002','80008510',1 from dual)  
select t.sec_id,t.type_t,t.prod_id,sum(t.amt_num)
from tmp_t t
group by t.sec_id,t.type_t,t.prod_id,t.amt_num
order by t.sec_id,t.type_t,t.prod_id,t.amt_num
　　结果如下：
　　

    总数是正确的。
　　
　　    group by 不使用sum:
　　   

  with tmp_t as(
select '0' as sec_id,'10000001' as type_t,'80008510' as prod_id,1 as amt_num from dual union all
select '0','10000001','80008510',1 from dual union all
select '0','10000001','80008510',1 from dual union all
select '0','10000001','80008510',1 from dual union all
select '0','10000002','80008510',1 from dual union all
select '0','10000002','80008510',1 from dual)  
select t.sec_id,t.type_t,t.prod_id,t.amt_num
from tmp_t t
group by t.sec_id,t.type_t,t.prod_id,t.amt_num
order by t.sec_id,t.type_t,t.prod_id,t.amt_num
　　结果如下，数量不对：
　　

 
　　全文完。