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

       同事写了个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

   结果如下，数量不对：

   

 

     全文完。