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

最新推荐文章于 2024-07-26 17:53:02 发布

oy538730875

最新推荐文章于 2024-07-26 17:53:02 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 工作记录文章标签：数据库

本文链接：https://blog.youkuaiyun.com/oy538730875/article/details/84597956

oracle 同时被 2 个专栏收录

52 篇文章

订阅专栏

工作记录

37 篇文章

订阅专栏

本文通过具体示例解释了在SQL查询中使用GROUP BY时如何正确应用聚合函数SUM来确保统计数据的准确性。当数据存在重复时，直接使用GROUP BY而不进行聚合可能会导致统计结果错误。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

结果如下，数量不对：