模拟多级表头的分组统计

原创 2004年10月12日 08:41:00

原帖地址:
http://community.csdn.net/Expert/topic/3434/3434688.xml?temp=3.246486E-03

--测试数据
create table 单位表 (单位代码 varchar(10),单位名称 varchar(50))
insert 单位表 values('01'      ,'中国单位')             --1级
insert 单位表 values('0101'    ,'山东单位')             --2级
insert 单位表 values('010101'  ,'山东济南单位')          --3级
insert 单位表 values('010102'  ,'山东青岛单位')          --3级
insert 单位表 values('01010201','山东青岛即默单位一')     --4级
insert 单位表 values('01010202','山东青岛即默单位二')     --4级
insert 单位表 values('0102'    ,'山西单位')              --2级
insert 单位表 values('010201'  ,'山西大同单位')          --3级
insert 单位表 values('0103'    ,'陕西单位')              --2级
insert 单位表 values('010301'  ,'陕西西安单位')          --3级
--insert 单位表 values('01030101'  ,'陕西西安A单位')          --3级
--insert 单位表 values('0103010101'  ,'陕西西安aa单位')          --3级
insert 单位表 values('010302'  ,'陕西咸阳单位')          --3级

create table 供应表 (物资编号 varchar(10),物资名称 varchar(50),单位代码 varchar(10),供应数量 int)
insert 供应表 values('0001','电子称','010101',1)
insert 供应表 values('0002','电动机','010101',1)
insert 供应表 values('0001','电子称','01010201',1)
insert 供应表 values('0002','电动机','01010201',1)
insert 供应表 values('0001','电子称','010201',1)
insert 供应表 values('0003','电动刷','010201',1)
insert 供应表 values('0004','电动车','010302',1)
go

/*--要求
 
 分级汇总,然后转置得到如下结果:
select '','','','中国单位','中国单位','中国单位','中国单位','中国单位','中国单位','中国单位','中国单位','中国单位'
union all select '','','','山东单位','山东单位','山东单位','山东单位','山西单位','山西单位','陕西单位','陕西单位','陕西单位'
union all select '','','',' ',' ','山东青岛单位','山东青岛单位',' ',' ',' ',' ',' '
union all select '序号','装备名称','合计','小计','山东济南单位','山东青岛即默单位一','山东青岛即默单位二','小计','山西大同单位','小计','陕西西安单位','陕西咸阳单位'
union all select '','总计','7','4','2','2','','2','2','1','','1'
union all select '1','(0001)电子称','3','2','1','1','','1','1','','',''
union all select '2','(0002)电动机','2','2','1','1','','','','','','' 
union all select '3','(0003)电动刷','1','','','','','1','1','','',''   
union all select '4','(0004)电动车','1','','','','','','','1','','1'


统计结果说明:
1.单位代码固定每两位为1级
2.统计单位表中所有的最末级,如果该单位在供应表中无数据,则对应显示为0
3.小计是根据二级单位合并得到
4.结果中的表头是分级的,一级单位在第一行,二级单位在第二行,如果该单位已经在统计数据,即"序号','装备名称','合计'"这行出现,则不再在对应的级数的表头里面出现
--*/

--查询处理
declare @i varchar(10),@s11 varchar(8000),@s12 varchar(8000),@s13 varchar(8000)
 ,@s2 varchar(8000),@s3 varchar(8000),@s14 varchar(8000)
 
select @s11='',@s12='',@s13='',@s14=''
 ,@s2='',@s3=''

select a=left(单位代码,4),b=left(单位代码,len(单位代码)-2),c=len(单位代码)-2
into # from 单位表 b
where not exists(select * from 单位表 where 单位代码 like b.单位代码+'__')
order by 单位代码

select @i=max(len(b)) from #
while @i>'0'
 select @s11=',@'+@i+' varchar(8000)'+@s11
  ,@s12=',@'+@i+'='''''''''','''''''','''''''''''+@s12
  ,@s13='
set @=null select @'+@i+'=@'+@i+'+case when @=a then '''' else '
   +case when @i>'4' then ''','''''''''' end' else ''',''''''+单位名称+'''''''' end' end
   +'+'',''''''+单位名称+'''''''',@=a from '
   +case when @i<='4' then '# a,单位表 b where left(a.b,'+@i+')=b.单位代码'
    else '(select 单位名称=case when a.c>='+@i+' then 单位名称 else '''' end,a.* from # a,单位表 b where left(a.b,'+@i+')=b.单位代码)a' end
   +@s13
  ,@s14='+'' union all select ''+@'+@i+@s14
  ,@i=@i-2
select @s12=stuff(@s12,1,1,'')
 ,@s14=stuff(@s14,1,13,'')

select @s2=@s2+','''+case when len(b.单位代码)=4 then '小计' else b.单位名称 end+''''
 ,@s3=@s3+case
  when len(b.单位代码)=4
  then ',['+b.单位名称+'_小计]=cast(sum(case left(单位代码,4) when '''+b.单位代码+''' then 供应数量 else 0 end) as varchar)'
  else ',['+b.单位名称+']=cast(sum(case 单位代码 when '''+b.单位代码+''' then 供应数量 else 0 end) as varchar)'
  end
from 单位表 b
where len(单位代码)=4
 or not exists(select * from 单位表 where 单位代码 like b.单位代码+'__')
order by 单位代码
set @s2=replace(@s2,'''','''''')

exec('
select 序号=cast('''' as varchar(10))
 ,装备名称=case
  when grouping(物资编号)=1 then ''总计''
  else  ''(''+物资编号+'')''+物资名称 end
 ,供应数量=cast(sum(供应数量) as varchar)'+@s3+'
into #t
from 供应表
group by 物资编号,物资名称 with rollup
having grouping(物资名称)=0 or grouping(物资编号)=1
order by grouping(物资编号) desc
declare @i int
set @i=-1
update #t set @i=@i+1,序号=case when @i=0 then '''' else cast(@i as varchar) end

declare @ varchar(10)'+@s11+'
select '+@s12+'
'+@s13+'
exec('''+@s14+'+''
union all
select ''''序号'''',''''装备名称'''',''''合计'''''+@s2+'
union all
select * from #t
'')
drop table #
')
go

--删除测试
drop table 单位表,供应表

/*--测试结果(自己看)--*/

 

多级分销系统(代理商佣金管理模块)设计概要(要求和数据库设计)

1. 数据库设计的原则 1) 准确记录账户基本信息,特别是状态。 2) 交易时要正确记录下交易信息和账户状态。 3) 交易记录是历史性的,不可篡改。 4) 交易是连续的,对时间...
  • zhouzhiwengang
  • zhouzhiwengang
  • 2016年05月05日 11:18
  • 4608

操作系统: 二级目录文件系统的实现(c/c++语言)

操作系统的一个课程设计,实现一个二级目录文件系统。 用disk.txt模拟磁盘,使用Help查看支持的命令及其操作方式,root为超级用户(写在disk.txt中) 文件的逻辑结构:流式文件。 物...
  • NK_test
  • NK_test
  • 2015年12月23日 19:20
  • 4559

使用JqGrid列表数据分组显示问题的解决方法

使用JqGrid列表数据分组显示的问题解决方法
  • tjdy7707
  • tjdy7707
  • 2015年06月13日 21:34
  • 5202

使用Group by分组统计.rar

  • 2010年07月02日 13:12
  • 43KB
  • 下载

对分组统计数据进行分析

  • 2014年02月18日 17:13
  • 1KB
  • 下载

分组统计报表

  • 2008年03月25日 14:23
  • 910B
  • 下载

Oracle分组统计

  • 2013年01月06日 09:53
  • 47KB
  • 下载

C# winform 动态分组统计

  • 2011年05月03日 11:38
  • 10KB
  • 下载

sql2000分组统计

  • 2011年04月12日 15:51
  • 357KB
  • 下载

分组统计报表

  • 2014年02月18日 16:35
  • 2KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:模拟多级表头的分组统计
举报原因:
原因补充:

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