3个字段group by，然后再统计的sql语句的问题！

oraclemch 2010-04-26 08:57:34

create table Z
(
RIQI VARCHAR2(10),
APN VARCHAR2(60),
ASA VARCHAR2(60),
SHICHANG NUMBER,
ACC_RESULT NUMBER(20,2),
FEE_NUMBER NUMBER(8) not null
);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-11', '16000171', '56723986', 2, 200, 100);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-12', '16000172', '68126337', 3, 300, 100);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-13', '16000173', '25881225', 2, 200, 200);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-13', '16000174', '64164232', 2, 200, 200);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-14', '16000175', '69177818', 8, 800, 200);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-14', '16000176', '63304666', 3, 300, 200);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-13', '16000177', '62092489', 5, 500, 100);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-13', '16000178', '65188457', 3, 300, 100);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-15', '16000176', '64168782', 1, 100, 300);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-15', '16000176', '53027205', 1, 100, 300);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-15', '16000176', '64212807', 3, 300, 300);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-13', '16000176', '64364641', 2, 200, 300);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-16', '16000179', '32313222', 1, 100, 100);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-17', '16000179', '53524520', 3, 300, 100);
commit;
测试数据如上所示
我想得到如下数据，建附件图片


我自己的sql如下：
select z.riqi,z.apn,z.fee_number,sum(z.acc_result),count(*) num
from z
group by z.fee_number ,z.riqi,z.apn
order by z.fee_number,z.riqi,z.apn
但是却不能按照fee_number再进行统计啦，各位有什么高招吗？做个样列sql吧，谢谢了！
...全文
379 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
tangren 2010-04-28
  • 打赏
  • 举报
 回复 
SELECT decode(grouping_id(z.riqi,z.fee_number),0,z.riqi,2,'小计',3,'总计') riqi,

       z.apn, z.fee_number, SUM(z.acc_result), COUNT(*) num

  FROM z

 GROUP BY rollup(z.fee_number,(z.riqi, z.apn))

 ORDER BY z.fee_number, z.riqi, z.apn
liqzhangq 2010-04-28
  • 打赏
  • 举报
 回复
顶一下
oracle QQ群：54775466
欢迎您的到来
大家一起探讨。
小灰狼W 2010-04-27
  • 打赏
  • 举报
 回复 
SELECT DECODE(GROUPING(riqi)+GROUPING(fee_number),'1','小记','2','合计',z.riqi) riqi,

  z.apn,

  DECODE(GROUPING(riqi),0,z.fee_number) fee_number,

  SUM(z.acc_result),

  COUNT(*) num

FROM z

GROUP BY grouping sets((z.fee_number ,z.riqi,z.apn),(z.fee_number),1)

ORDER BY z.fee_number,

  z.riqi,

  z.apn
liuhuan851002 2010-04-27
  • 打赏
  • 举报
 回复
太长了 没时间看啊.
帮你顶下.
ladybirds2008 2010-04-27
  • 打赏
  • 举报
 回复
真难。。想学习一下啦。。。帮你顶贴。。。。lz要给点辛苦分哦。。。。
心中的彩虹 2010-04-27
  • 打赏
  • 举报
 回复 


--互相学习相互提升

SQL> edi

已写入 file afiedt.buf



  1  select case when grouping(riqi)=1 and grouping(apn)=1 and grouping(FEE_NUMBER)=1

  2  then '总计' else riqi end riqi,

  3  case when grouping(apn)=1 and grouping(riqi)=1 and grouping(FEE_NUMBER)=0

  4  then '小计' else apn end apn,

  5  FEE_NUMBER,

  6  sum(ACC_RESULT) jine,count(*) num

  7  from z

  8  group by rollup(FEE_NUMBER,riqi,apn)

  9* having  grouping(apn)=0 or grouping(riqi)=1

SQL> /



RIQI       APN                  FEE_NUMBER       JINE        NUM

---------- -------------------- ---------- ---------- ----------

2009-10-11 16000171                    100        200          1

2009-10-12 16000172                    100        300          1

2009-10-13 16000177                    100        500          1

2009-10-13 16000178                    100        300          1

2009-10-16 16000179                    100        100          1

2009-10-17 16000179                    100        300          1

           小计                        100       1700          6

2009-10-13 16000173                    200        200          1

2009-10-13 16000174                    200        200          1

2009-10-14 16000175                    200        800          1

2009-10-14 16000176                    200        300          1

           小计                        200       1500          4

2009-10-13 16000176                    300        200          1

2009-10-15 16000176                    300        500          3

           小计                        300        700          4

总计                                             3900         14



已选择16行。
dingjun123 2010-04-26
  • 打赏
  • 举报
 回复 




SQL> select z.riqi,z.apn,case when  z.riqi is null and z.apn is null and z.fee_number is not null then '小计'

  2  when  z.riqi is null and z.apn is null and z.fee_number is  null then '合计'

  3  else  to_char(z.fee_number) end fee_number ,sum(z.acc_result),count(*) num

  4   from z

  5   group by grouping sets(rollup(z.fee_number,(z.riqi,z.apn)))

  6   order by z.fee_number,z.riqi,z.apn ;

 

RIQI     APN                                                       FEE_NUMBER                     SUM(Z.ACC_RESULT)        NUM

---------- ------------------------------------------------------------ ---------------------------------------- ----------------- ----------

2009-10-11 16000171                                               100                                                  200          1

2009-10-12 16000172                                               100                                                  300          1

2009-10-13 16000177                                               100                                                  500          1

2009-10-13 16000178                                               100                                                  300          1

2009-10-16 16000179                                               100                                                  100          1

2009-10-17 16000179                                               100                                                  300          1

                                                                        小计                                                1700          6

2009-10-13 16000173                                               200                                                  200          1

2009-10-13 16000174                                               200                                                  200          1

2009-10-14 16000175                                               200                                                  800          1

2009-10-14 16000176                                               200                                                  300          1

                                                                        小计                                                1500          4

2009-10-13 16000176                                               300                                                  200          1

2009-10-15 16000176                                               300                                                  500          3

                                                                        小计                                                 700          4

                                                                        合计                                                3900         14

 

16 rows selected
dingjun123 2010-04-26
  • 打赏
  • 举报
 回复 


SQL>  select z.riqi,z.apn,z.fee_number,sum(z.acc_result),count(*) num

  2   from z

  3   group by grouping sets(rollup(z.fee_number,(z.riqi,z.apn)))

  4   order by z.fee_number,z.riqi,z.apn ;

 

RIQI     APN                                                       FEE_NUMBER SUM(Z.ACC_RESULT)        NUM

---------- ------------------------------------------------------------ ---------- ----------------- ----------

2009-10-11 16000171                                                      100               200          1

2009-10-12 16000172                                                      100               300          1

2009-10-13 16000177                                                      100               500          1

2009-10-13 16000178                                                      100               300          1

2009-10-16 16000179                                                      100               100          1

2009-10-17 16000179                                                      100               300          1

                                                                               100              1700          6

2009-10-13 16000173                                                      200               200          1

2009-10-13 16000174                                                      200               200          1

2009-10-14 16000175                                                      200               800          1

2009-10-14 16000176                                                      200               300          1

                                                                               200              1500          4

2009-10-13 16000176                                                      300               200          1

2009-10-15 16000176                                                      300               500          3

                                                                               300               700          4

                                                                                                3900         14

 

16 rows selected
cyousor 2010-04-26
  • 打赏
  • 举报
 回复 


Select z.Riqi, z.Apn, z.Fee_Number, Sum(z.Acc_Result), Count(*) Num

  From z

 Group By Rollup(z.Fee_Number, (z.Riqi, z.Apn, z.Fee_Number))

 Order By z.Fee_Number, z.Riqi, z.Apn


不好意思，没看清楚啊
这个？？！
iqlife 2010-04-26
  • 打赏
  • 举报
 回复 
RIQI	APN	FEE_NUMBER	SUM(Z.ACC_RESULT)	NUM

2009-10-11	16000171	100	200	1

2009-10-12	16000172	100	300	1

2009-10-13	16000177	100	500	1

2009-10-13	16000178	100	300	1

2009-10-16	16000179	100	100	1

2009-10-17	16000179	100	300	1

	                	100	1700	6

2009-10-13	16000173	200	200	1

2009-10-13	16000174	200	200	1

2009-10-14	16000175	200	800	1

2009-10-14	16000176	200	300	1

	                	200	1500	4

2009-10-13	16000176	300	200	1

2009-10-15	16000176	300	500	3

                		300	700	4

	                		3900	14
iqlife 2010-04-26
  • 打赏
  • 举报
 回复 
 select * from (

      select z.riqi,z.apn,z.fee_number,sum(z.acc_result),count(*) num  

       from z  

       group by rollup(z.fee_number ,z.riqi,z.apn)

       order by z.fee_number,z.riqi,z.apn 

   )where (riqi is  null and apn is  null) or (riqi is not null and apn is not null)


结果如下:
RIQI APN FEE_NUMBER SUM(Z.ACC_RESULT) NUM
2009-10-11 16000171 100 200 1
2009-10-12 16000172 100 300 1
2009-10-13 16000177 100 500 1
2009-10-13 16000178 100 300 1
2009-10-16 16000179 100 100 1
2009-10-17 16000179 100 300 1
100 1700 6
2009-10-13 16000173 200 200 1
2009-10-13 16000174 200 200 1
2009-10-14 16000175 200 800 1
2009-10-14 16000176 200 300 1
200 1500 4
2009-10-13 16000176 300 200 1
2009-10-15 16000176 300 500 3
300 700 4
3900 14
oraclemch 2010-04-26
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 vber1010 的回复:]
啥意思》？这样吗？

select z.riqi,z.apn,z.fee_number,sum(z.fee_number) over (partition by z.fee_number) a,sum(z.acc_result),count(*) num
from z
group by z.fee_number ,z.riqi,z.apn
order by z.fee_nu……
[/Quote]

不是的，你看我的图片上面的结果集合吧。
cyousor 2010-04-26
  • 打赏
  • 举报
 回复 


select z.riqi,z.apn,z.fee_number,sum(z.acc_result),count(*) num  

 from z  

 group by Rollup (z.fee_number ,z.riqi,z.apn)

 order by z.fee_number,z.riqi,z.apn
vber1010 2010-04-26
  • 打赏
  • 举报
 回复
啥意思》？这样吗？

select z.riqi,z.apn,z.fee_number,sum(z.fee_number) over (partition by z.fee_number) a,sum(z.acc_result),count(*) num
from z
group by z.fee_number ,z.riqi,z.apn
order by z.fee_number,z.riqi,z.apn
SQL Server数据库基础到精通课程
本课程讲解从最开始的 创建数据库、创建数据表、已经数据的写入、查询、修改更新、删除，字段运算、字段运算统计、分类汇总、数据分析、条件判断，条件筛选及一些常用的SQL数据库语句，变量定义，字段约束、等等，...
SQL中GROUP BY语句介绍
本文主要介绍 SQL（Structured Query Language）中 GROUP BY 语句的相关知识，同时通过用法示例介绍 GROUP BY 语句的常见用法。 1 概述 GROUP BY 语句通常用于配合聚合函数（如 COUNT()、MAX() 等），根据一个或多...
sql：常用：group by 多个字段
 group by 一般和聚合函数一起使用才有意义,比如 count sum avg等,使用group by的两个要素:  (1) 出现在select后面的字段 要么是是聚合函数中的,要么就是group by 中的.  (2) 要筛选结果 可以先使用where 再用...
SQL语句之分组查询--GROUP BY（group by）
sql语句select之分组查询（group by） 分组查询 语法： select 分组函数，列（要求出现在group by的后面） from 表 where 筛选条件 group by 分组的列表 order by 子句 注意：查询列表比较特殊，要求是分组函数和...
sql中 group by多个字段，对所有字段做group by
首先是大家都明白的一个group by操作，用B做group by的条件，a一共有4个，b一共有6个 select count(A),B from test_table group by B 同理 select count(A),C from test_table group by C 两个group by 其实就是...
Oracle

17,086

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章