97.分组交叉表、复杂交叉表

 

案例1-分组交叉表：

案例3：

CREATE proc p_qry
@JFKMBID varchar(20),
@JFKMBSYSID varchar(1000),
@date1 datetime,
@date2 datetime,
@r_count int
as
set nocount on

--分拆 @JFKMBSYSID
declare @i int
set @i=len(@JFKMBSYSID)
if isnull(@i,0)<1 return
set rowcount @i
select id=identity(int) into #t from syscolumns a,syscolumns b
set rowcount @i
select jFKMBSYSID=substring(@JFKMBSYSID,id,patindex('%[,;]%',stuff(@JFKMBSYSID+',',1,id,'')))
	,gid=case when substring(','+@JFKMBSYSID,id,1)=',' then 0 else 1 end,sid=0
into #t1 from #t
where substring(','+@JFKMBSYSID,id,1) in(',',';')
order by id

--生成分组标志
select @i=1
update #t1 set @i=case when gid=0 then @i else @i+1 end,gid=@i

--生成处理数据
select a1.gid,a2.PX,b.qydm,cnt=count(*),sid=0
into #t2
from #t1 a1,S_IntegralMode a2,S_PntegralFactInfo b
where a1.jFKMBSYSID=a2.jFKMBSYSID
	and a2.jFKMBSYSID=b.jFKMBSYSID
	and b.slsj>=convert(char(10),@date1,120)
	and b.slsj=@r_count)

--生成交叉表处理标志
declare @gid int,@PX int
set @i=1
update #t2 set @i=case when @gid=gid 
		then case when @PX=PX then @i else @i+1 end
		else 1 end
	,@gid=gid,@PX=PX,sid=@i

--生成交叉表处理语句
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@s4 varchar(8000)
select @s1='',@s2='',@s3='',@s4=''
	,@i=max(sid) from #t2
while @i>0
	select @s1=',[col'+rtrim(@i)
		+']=cast(sum(case sid when '+rtrim(@i)
		+' then cnt else 0 end) as varchar)'+@s1
		,@s2=',[col'+rtrim(@i)
		+']=max(case sid when '+rtrim(@i)
		+' then cast(PX as varchar) else '''' end)'+@s2
		,@s3=',a.[col'+rtrim(@i)+']'+@s3
		,@i=@i-1

--显示结果
exec('
	select qymc=isnull(b.qymc,a.qydm)'+@s3+'
	from(
		select gid,qydm,s1=1'+@s1+' from #t2 group by gid,qydm
		union all
		select gid,''查询分组''+rtrim(gid),s1=0'+@s2+' from #t2 group by gid
		union all
		select gid,''分组''+rtrim(gid)+''合计'',s1=2'+@s1+' from #t2 group by gid
	)a left join S_Enterprise b on a.qydm=b.qydm
	order by a.gid,a.s1,a.qydm
')
GO

exec p_qry '4-2005','45,46,47,48;49,50,51,52,53;54,55;56,57,58;59','2005-1-1','2005-1-31',1
go

drop proc p_qry

/*--测试结果

qymc	
------
查询分组1	1	2	3	4	
象山医药药材有限公司医药商店	1	1	1	1	0
象山同仁堂大药房药业有限公司	3	3	3	3	0
分组1合计	4	4	4	4	0
查询分组2	5	6	7	8	9
象山医药药材有限公司医药商店	1	1	1	1	1
象山同仁堂大药房药业有限公司	3	3	3	3	3
分组2合计	4	4	4	4	4
查询分组3	10	11			
象山医药药材有限公司医药商店	1	1	0	0	0
象山同仁堂大药房药业有限公司	3	3	0	0	0
分组3合计	4	4	0	0	0
查询分组4	12	13	14		
象山医药药材有限公司医药商店	1	1	1	0	0
象山同仁堂大药房药业有限公司	3	3	3	0	0
分组4合计	4	4	4	0	0
查询分组5	15				
象山医药药材有限公司医药商店	1	0	0	0	0
象山同仁堂大药房药业有限公司	3	0	0	0	0
分组5合计	4	0	0	0	0
--*/

 

案例2-复杂交叉表：

--测试 --测试数据 create table 表(编号 int,分类一 varchar(10),分类二 varchar(10),分类三 varchar(10)) insert 表 select 1,'M1','D1','F1' union all select 1,'M1','D2','F2' union all select 1,'M2','D3','F3' union all select 1,'M2','D3','F4' union all select 1,'M2','D3','F5' union all select 1,'M2','D4','F2' union all select 1,'M2','D4','F7' union all select 1,'M2','D5','F1' union all select 1,'M2','D5','F3' union all select 1,'M3','D6','F8' union all select 1,'M3','D7','F6' go --处理方法2. declare @s varchar(8000) select @s='' select @s=@s+',['+分类一+'_1] varchar(10) default '''',[' +分类一+'_2] varchar(10) default ''''' from 表 group by 分类一 exec('create table #t(id int identity(1,1)'+@s+') declare @s varchar(8000) declare tb cursor local for select s=''declare @t table(id int identity(1,1),a varchar(10),b varchar(10)) declare @r int insert @t select 分类二,分类三 from 表 where 分类一=''''''+分类一+'''''' set @r=@@rowcount update #t set [''+分类一+''_1]=b.a,[''+分类一+''_2]=b.b from #t a join @t b on a.id=b.id if @@rowcount<@r insert #t([''+分类一+''_1],[''+分类一+''_2]) select a.a,a.b from @t a left join #t b on a.id=b.id where b.id is null'' from 表 group by 分类一 open tb fetch next from tb into @s while @@fetch_status=0 begin exec(@s) fetch next from tb into @s end close tb deallocate tb select * from #t ') go --删除测试 drop table 表 /*--测试结果 M1 M1 M2 M2 M3 M3 ---------- ---------- ---------- ---------- ---------- ---------- D1 F1 D3 F3 D6 F8 D2 F2 D3 F4 D7 F6 D3 F5 D4 F2 D4 F7 D5 F1 D5 F3 --*/
 

 

案例3-复杂交叉表：

--测试

--测试数据
create table 表(编号 int,分类一 varchar(10),分类二 varchar(10),分类三 varchar(10))
insert 表 select 1,'M1','D1','F1'
union all select 1,'M1','D2','F2'

union all select 1,'M2','D3','F3'
union all select 1,'M2','D3','F4'
union all select 1,'M2','D3','F5'
union all select 1,'M2','D4','F2'
union all select 1,'M2','D4','F7'
union all select 1,'M2','D5','F1'
union all select 1,'M2','D5','F3'

union all select 1,'M3','D6','F8'
union all select 1,'M3','D7','F6'
go

--处理
select id=0,* into #t from 表 order by 编号,分类一--,分类二,分类三
declare @a varchar(10),@i int
update #t set @i=case @a when 分类一 then @i+1 else 0 end,id=@i,@a=分类一

declare @fd varchar(8000),@s varchar(8000)
	,@t1 varchar(10),@t2 varchar(10)
select @fd='',@s='',@t1='',@t2=''
select @t2='['+分类一+']'
	,@fd=@fd+','+@t2+'=isnull('+@t2+'.分类二,''''),'+@t2+'=isnull('+@t2+'.分类三,'''')'
	,@s=case @t1 when '' then '(select * from #t where 分类一='''+分类一+''')'+@t2 
		else @s+' full join(select * from #t where 分类一='''+分类一+''')'+@t2+' on '+@t1+'.id='+@t2+'.id ' end
	,@t1=@t2
from #t group by 分类一

select @fd=substring(@fd,2,8000)
exec('select '+@fd+'
from '+@s)

--删除处理临时表
drop table #t
go 

--删除测试
drop table 表

/*--测试结果

M1         M1         M2         M2         M3         M3         
---------- ---------- ---------- ---------- ---------- ---------- 
D1         F1         D3         F3         D6         F8
D2         F2         D3         F4         D7         F6
                      D3         F5                    
                      D4         F2                    
                      D4         F7                    
                      D5         F1                    
                      D5         F3                             

--*/