98.格式化报表

刺骨的言语ヽ痛彻心扉 2024-02-19 18:58 14阅读 0赞

--格式化报表
    
    --下面是测试
    
    --测试数据
    create table 表(店名 varchar(4),电话 varchar(6),商品 varchar(2),数量 int,价格 int)
    insert into 表 
    select '店1','123456','Ax',1,30
    union select '店2','223456','Az',3,35
    union select '店3','223456','Ay',2,36
    union select '店4','223456','Az',3,45
    union select '店5','223456','Au',7,36
    union select '店2','223456','Aa',3,35
    union select '店3','223456','Ab',2,36
    union select '店4','223456','Ac',3,45
    union select '店5','223456','Au',7,36
    union select '店2','223456','Ad',3,35
    union select '店2','223456','Ae',2,36
    union select '店2','223456','Af',3,45
    union select '店2','223456','Ag',7,36
    union select '店2','223456','Ah',3,35
    union select '店2','223456','Ai',2,36
    union select '店2','223456','Aj',3,45
    union select '店2','223456','Ak',7,36
    go
    
    --实现查询的存储过程
    create proc p_qry
    @size int=6		--每行的商品数量
    as
    set nocount on
    select 店名,gid=0,gid1=0
    	,商品=cast(商品 as char(6))+space(4)
    		+cast(数量 as char(6))+space(4)
    		+cast(价格 as char(4))
    into #t from 表 order by 店名
    declare @dm varchar(4),@i int,@g int
    update #t set @i=case when @dm=店名 and @i<@size then @i+1 else 1 end
    		,@g=case when @dm=店名 then case @i when 1 then @g+1 else @g end else 1 end
    	,gid=@i,gid1=@g,@dm=店名
    
    declare @s1 varchar(8000),@s2 varchar(8000)
    select @s1='',@s2=''
    while @size>0
    	select @s1=',['+cast(@size as varchar)+']=max(case gid when '
    		+cast(@size as varchar)+' then 商品 else '''' end)'
    		+@s1
    		,@s2='+['+cast(@size as varchar)+']'+@s2
    		,@size=@size-1
    set @s2=substring(@s2,2,8000)
    exec('
    select re from(
    	select 店名,id=1,re='+@s2+' from(
    	select 店名'+@s1+' from #t group by 店名,gid1
    	)a 
    	union all
    	select 店名,id=0
    		,re=''店名：''+店名
    		+space(4)+''电话：''+电话
    		+space(4)+''总价：''+cast(总价 as varchar)
    	from(
    		select 店名,电话=max(电话),总价=sum(数量*价格) 
    		from 表 group by 店名
    	)a
    	union all
    	select 店名,id=3	,re=replicate(''-'',100)
    	from 表 group by 店名
    ) a order by 店名,id
    ')
    set nocount off
    go
    
    --调用:
    exec p_qry 3
    go
    
    --删除测试环境
    drop proc p_qry
    drop table 表
    
    /*--测试效果
    
    re                                                                                                   
    -------------------------------------------------------------------------
    店名：店1    电话：123456    总价：30
    Ax        1         30  
    -------------------------------------------------------------------------
    店名：店2    电话：223456    总价：1338
    Aa        3         35  Ad        3         35  Ae        2         36  
    Af        3         45  Ag        7         36  Ah        3         35  
    Ai        2         36  Aj        3         45  Ak        7         36  
    Az        3         35  
    -------------------------------------------------------------------------
    店名：店3    电话：223456    总价：144
    Ab        2         36  Ay        2         36  
    -------------------------------------------------------------------------
    店名：店4    电话：223456    总价：270
    Ac        3         45  Az        3         45  
    -------------------------------------------------------------------------
    店名：店5    电话：223456    总价：252
    Au        7         36  
    -------------------------------------------------------------------------
    --*/