sql 小计合计排序

原创 2012年03月28日 17:08:05
很久没写东西了!

学校 班级 学号 姓名 分数
学校A 班级01 01 小林 90
学校A 班级01 02 小李 60
  班级小计 人数 2 150
学校A 班级02 01 小强 75
  班级小计 人数 1 75
  学校合计 人数 3 225
学校B 班级01 01 小小 85
  班级小计 人数 1 85
  学校合计 人数 1 85
  全县合计 学校数:2 学生数:4 总分:305

像这样的列表里面出现小计和合计的情况在统计报表中是经常出现的, 在这些报表里面, 怎么排序就是一个难题了!

上面的表格, 学生的排序是按学号的大小排序的, 班级目前是也是使用大小排序, 但是学校不是大小排序, 而是固定排序, 对于这种情况, 那么我们就需要自己手工给学校做一个序号, 方便他们排序了.

但是怎么把"班级小计","学校合计"和"全县合计" 插入到正确的位置呢?

我们正常的排序是:     order by  学校, 班级, 学号

  • "班级小计" 是要放在某个班级的最后一位学生后面, 最后一位是号码是什么, 我们可以求出来, 但是这样写起来每次都要进行一次最后一位的判断! 这样显然是低效和繁琐的.
  • "学校合计" 是要放在某个学校的班级信息的最后一位.
  • "全县合计" 是要放在某个县的学校信息的最后一位.

我们可以从中看出, "最后一位" 这个是关键, 怎么确定最后一位呢?  老师可以帮我们安排学位, 为什么不能帮我们的小计直接安排在最后呢? 学校可以安排班级顺位, 那由它直接安排合计在最后不就行了吗? 所以

  • "班级小计" order by 学校,班级
  • "学校合计" order by 学校

由统计的范围来决定小计合计的排序位置, 简单来说,

" 学校A ->班级01->班级合计列"  直接赋予"班级010" 这样的信息,  这样通过order by 学校,班级 , 班级01 的"班级小记" 就会在该班级中排最后(当然不是说一定在后面加"0"的, 具体要加什么可以由自己决定, 可以参考一些排序优先级的,如果是oracle, null 默认是最大值, 要注意不要让班级01的班级小记掉到其他班级那里去了 ).

" 学校A->学校合计" 直接赋予"学校A0" 这样的信息, 理由同上.

 

学校 班级
县层面排序值
(自定义的)
学校排序值 班级排序值 学号 姓名 分数
学校A 班级01 01 学校A 班级01 01 小林 90
学校A 班级01 01 学校A 班级01 02 小李 60
  班级小计 01 学校A 班级010 人数 2 150
学校A 班级02 01 学校A 班级02 01 小强 75
  班级小计 01 学校A 班级020 人数 1 75
  学校合计 01 学校A0   人数 3 225
学校B 班级01 02 学校B 班级01 01 小小 85
  班级小计 02 学校B 班级010 人数 1 85
  学校合计 02 学校B0   人数 1
85
  全县合计 99     学校数:2 学生数:4 总分:305

注意里面的 蓝色字体,    这样就简单了

   order 县层面排序值,学校排序值,班级排序值, 学号

一般思路:

select 调整显示内容

from (

      select * from

       (select 学生信息,学号,班级,学校, 县 as 县层面排序值, 学校 as 学校排序值, 班级 as 班级排序值, 类型标识(用于区分数据的层次)

       union all

       select 班级小计(count(学号), sum(分数)), null 班级排序值,.............

           group by 县级,学校,班级

       union all

       select 学校合计(count(学号), sum(分数)), null 学校排序值,.............

            group by 县级,学校

       union all

       select 全县合计(count(学号), sum(分数)), null 县层面排序值,.............)

            group by 县级

       order 县层面排序值,学校排序值,班级排序值, 学号

)

当然这些在数据量不大的情况还行, 如果数据量大的话, 各种处理方式, 最好把统计的数据缓存起来, 不要重复执行sum, count 这些耗时的操作.

--------------------------------------可爱的分割线---------------------------------

像上面这样的统计, 还有更简单的做法, 那就是使用 group by rollup(学校, 班级,(学号,姓名)),或者用group by rollup(学校,班级,学号),姓名字段用max(姓名) as 姓名

学校 班级 学号 姓名 分数(sum) 人数(count)
grouping_id(学校,班级,学号)
学校A 班级01 01 小林 90 1 0
学校A 班级01 02 小李 60 1 0
学校A 班级02 01 小强 75   0
学校B 班级01 01 小小 85   0
学校A 班级01     150 2 1
学校A 班级02     75 1 1
学校B 班级01     85 1 1
学校A



225
3
3
学校B



85
1
3
        305 4 7

在前面说过了, oracle 排序中null 默认是最大值, 所以如果是升序, 直接order by  学校, 班级, 学号 就行了, 很方便吧.

grouping_id() 方法是配合group by rollup 使用的, 这里我们用来区分数据层次, 我们可以看到0,1,3,7 这样的数字, 可能不好理解,

但是转为二进制就好理解了, 111 =7   011=3  001=1 000=0,      简单来说, 统计的范围越大, 数字越大,

group by rollup(学校, 班级,(学号,姓名))

 分解成       group by 学校, 班级, (学号,姓名) + group by 学校, 班级 + group by 学校 + group by null

grouping_id(学校,班级,学号)         000          001     011      111

一般思路:

select 调整显示内容

from (

        select 学校, 班级, 学号, 姓名, sum(分数) 分数, count(学号) 学号, grouping_id(学校,班级,学号)  gp_num

        ...................... 

        group by rollup(学校, 班级,(学号,姓名))

        order by  学校, 班级, 学号

)

这种写法跟上面的写法, 效率差距不大, 但是简洁很多, 当然不同的数据库对应null 的排序优先级是不一致的, 用第一种方法通用性强一点, 但是写起来代码很长.

Sql分组合计小计查询

效果如下: 用的表和字段: -table 实收水费:hx_t_received  --字段收费部门id:hx_fdepartmentid  1、收费部门名称:hx_fdepartmentname ...
  • zouyujie1127
  • zouyujie1127
  • 2014年11月22日 16:38
  • 5655

用SQL实现统计报表中的“小计”和“合计”

 用SQL实现统计报表中的“小计”和“合计”   在开发统计报表的过程中,经常会碰到在查询到的数据集中,插入一些小计行和合计行。比如在烤烟系统中,几乎每个统计报表都需要加入“合计”行,还有不少涉及到烟...
  • xuyuan77
  • xuyuan77
  • 2007年06月06日 15:09
  • 3571

ORACLE SQL——一句SQL搞定小计与总计

主要ORACLE 函数  wm_concat   的应用: 先看TIPTOP 界面的效果图(如下) 举例:建立测试table,insert数据,查看效果 1.建测试表 ===========...
  • fmx121586
  • fmx121586
  • 2012年09月13日 14:19
  • 2290

Oracle关于Group by合计、小计的使用

方法:group by rollup(col1,col2) group by cube(col1,col2) 具体例子: select G_GOODS_OUT.DEPTID, sum(decod...
  • ceclar123
  • ceclar123
  • 2013年06月06日 15:48
  • 8729

Sql使用WITH ROLLUP 进行分类汇总及小计功能

效果如下:测试代码如下:CREATE TABLE #test    (      Name VARCHAR(10) ,      [procedure] CHAR(1) ,      model VA...
  • greystar
  • greystar
  • 2015年10月08日 15:55
  • 6328

VSFlexGrid 合计项与排序问题的解决方法

Private Sub VS1_BeforeSort(ByVal Col As Long, Order As Integer) '排序前删除合计项        With VS1        If ...
  • AYongSoft
  • AYongSoft
  • 2010年09月28日 16:39
  • 2063

sql 小计 合计 排序

学校 班级 学号 姓名 分数 学校A 班级01 01 小林 90 学校A 班级01 02 小李 60   班级小计 人数 2 15...
  • sjzs5590
  • sjzs5590
  • 2012年04月12日 10:09
  • 1282

Oracle分组扩展函数的使用(主要增加小计及合计金额)

在oracle及其它数据库中通常使用group by 进行数据分组,oracle在group by 的基础上提供了一组分组的扩展函数,这组扩展函数主要用于增加小计及总计记录,主要包括用于group b...
  • fycghy0803
  • fycghy0803
  • 2012年03月26日 12:07
  • 1903

cognos本页合计+本表合计,不分页显示

1.需求:  1.每三十条数据做一次合计,外加一个本表合计  2.并且每三十条数据后要加说明 如图: 2.实现过程 1.拖入列表,并且按照分组序号进行分组 2.菜单-结构-页眉和页...
  • xzl1203
  • xzl1203
  • 2011年08月04日 18:05
  • 3076

oracle group by rollup实现小计、合计

oracle group by rollup实现小计、合计
  • angeiqiang
  • angeiqiang
  • 2015年12月18日 16:54
  • 7877
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:sql 小计合计排序
举报原因:
原因补充:

(最多只允许输入30个字)