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 小计 合计 排序

学校 班级 学号 姓名 分数 学校A 班级01 01 小林 90 学校A 班级01 02 小李 60   班级小计 人数 2 15...

SQL小计合计并排序

--说明:个人学习笔记,实现小计合计显示,分组按BANK_ID+OP_DATE升序排序 --测试数据 CREATE TABLE #TB(ID VARCHAR(10),BANK_ID VARCHAR(1...

用SQL进行报表的小计和合计

最近在进行报表的测试,表中涉及到了添加行小计条件,当时在想用SQL怎么来实现行小计呢,于是上网搜素,还真有类似的文章,转来学习下又增长了知识。 但是有个疑问,一个表中针对一个数据列,用不同小计条件能...

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

客户提出需求,针对某一列分组加上小计,合计汇总。网上找了一些有关SQL加合计的语句。都不是很理想。决定自己动手写。 思路有三个: 1.很多用GROUPPING和ROLLUP来实现。   优点实现...

SQL游标小计

  • 2014年08月27日 09:18
  • 1KB
  • 下载

SQL学习(三) 轻松实现汇总小计(ROLLUP/CUBE)

前言        我们在报表书写的时候,往往有需要作分类小计的情况,过去我们的技术人员常常是通过union all 来实现,结果导致报表中SQL语句繁杂,执行效率低不说,而且会为后期维护增加不必要...

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

主要ORACLE 函数  wm_concat   的应用: 先看TIPTOP 界面的效果图(如下) 举例:建立测试table,insert数据,查看效果 1.建测试表 ===========...

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

方法:group by rollup(col1,col2) group by cube(col1,col2) 具体例子: select G_GOODS_OUT.DEPTID, sum(decod...

分类统计数据,按类别统计小计,最后再合计

分类统计数据,按类别统计小计,最后再合计 前台jsp: 日期: ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:sql 小计合计排序
举报原因:
原因补充:

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