SQL之循环实现月份从1-12横表展 示数据

CREATE OR REPLACE FUNCTION "public"."realquota_rx_yg"("tmpfilename" text, "beginmonth" text, "endmonth" text)
  RETURNS "pg_catalog"."text" AS $BODY$
     --RX纯销报表
     declare 

            sql  text ;
            
            tmpfilename2 text;
            
            tmpfilename3 text;
            columnList text;
            ratemonth text ;
            ibeginmonth numeric;
            iendmonth numeric;
            cond1 text;  --
            cond2 text;
            cond3 text;
            cond4 text;

            
             --横式表格'1'，竖式表格 '0' styles
    begin 

            tmpfilename2 = tmpfilename || '2';
            tmpfilename3 = tmpfilename || '3';
            ibeginmonth = cast(beginmonth as int);
            iendmonth = cast(endmonth as int);
            
            -- 建立临时表1
            sql = 'create table if not exists ' ||  tmpfilename || '(
            
                            yearmonth varchar(6),
                            
                            clientId int4,
                            
                            departmentid int4,
                                                             
                            goodscode char(8),
                            
                            goods_category int4,
                            
                            qty decimal(20,2),
                            
                            amount decimal(20,2)
                            
                            ) ';
                            execute sql;
                            
                            
                            -- 插入前先删除 本月流向数据, 相当于最新月更新
                        
                            sql = 'alter table '||  tmpfilename  ||' REPLICA IDENTITY FULL';
                            execute sql;
                
                            
                            --插入正常流向数据 
                                            
                            sql = 'insert into ' ||  tmpfilename;
                            
                            sql = sql || '(yearmonth,clientId,departmentid,goodscode,goods_category,qty,amount) ';
                            
                            sql = sql || ' select yjlx_lxqj,yjlx_khbh,yjlx_xybm,yjlx_wlbh,yjlx_wllb,sum(yjlx_sl),sum(yjlx_je) ';
                            
                            sql = sql || ' from yjlx ';
                            
                            sql = sql || ' where yjlx_lxqj between ''' || beginmonth || ''' and  '''|| endmonth ||''' ';
                            
                            sql = sql || ' and yjlx_tx = ''RX'' and yjlx_sfcx=1 ';
                            
                            sql = sql || ' and yjlx_xybm > 0 ';
                            
                            sql = sql || ' and yjlx_je > 0 and yjlx_je is not null ';
                            
                            sql = sql || ' group by yjlx_lxqj,yjlx_khbh,yjlx_xybm,yjlx_wlbh,yjlx_wllb ';
                            RAISE NOTICE '插入正常流向数据： %',sql;
                            execute sql;
                            
                            
                            
                            --插入调整流向数据
                            
                            sql = 'insert into ' ||  tmpfilename;
                            
                            sql = sql || '(yearmonth,clientId,departmentid,goodscode,goods_category,qty,amount) ';
                            
                            sql = sql || ' select yjlx_lxqj,yjlx_khbh,yjlx_xybm,yjlx_wlbh,yjlx_wllb,sum(yjlx_sl),sum(yjlx_je) ';
                            
                            sql = sql || ' from tzlx ';
                            
                            sql = sql || ' where yjlx_lxqj between ''' || beginmonth || ''' and  '''|| endmonth ||''' ';
                            
                            sql = sql || ' and yjlx_tx = ''RX'' and  yjlx_sfcx=1 ';
                            
                            sql = sql || ' and yjlx_xybm > 0 ';
                            
                            sql = sql || ' and yjlx_je > 0 and yjlx_je is not null ';

                            sql = sql || ' group by yjlx_lxqj,yjlx_khbh,yjlx_xybm,yjlx_wlbh,yjlx_wllb ';
                            
                            execute sql;
                            RAISE NOTICE '插入调整流向数据： %',sql;
                            
                            -- 建立临时表2
                            sql = 'create table if not exists ' ||  tmpfilename2;
                            sql = sql ||  '(
                            
                                                year int4 ,
                                                
                                                yearmonth char(6) ,
                                                
                                                bgid int4,
                                                
                                                bgname  varchar(20) ,
                                                
                                                bigareaid  int4 ,
                                                
                                                bigareaname varchar(20),
                                                
                                                departmentid int4 ,
                                                
                                                departmentname varchar(20) ,
                                                username varchar(20) ,
                                                total_qty decimal(10,2) not null defalut 0,
                                                total_amount decimal(10,2) not null defalut 0,
                                                m1_qty decimal(10,2) not null defalut 0,
                                                m1_amount decimal(10,2) not null defalut 0,
                                                m2_qty decimal(10,2) not null defalut 0,
                                                m2_amount decimal(10,2) not null defalut 0,
                                                m3_qty decimal(10,2) not null defalut 0,
                                                m3_amount decimal(10,2) not null defalut 0,
                                                m4_qty decimal(10,2) not null defalut 0,
                                                m4_amount decimal(10,2) not null defalut 0,
                                                m5_qty decimal(10,2) not null defalut 0,
                                                m5_amount decimal(10,2) not null defalut 0,
                                                m6_qty decimal(10,2) not null defalut 0,
                                                m6_amount decimal(10,2) not null defalut 0,
                                                m7_qty decimal(10,2) not null defalut 0,
                                                m7_amount decimal(10,2) not null defalut 0,
                                                m8_qty decimal(10,2) not null defalut 0,
                                                m8_amount decimal(10,2) not null defalut 0,
                                                m9_qty decimal(10,2) not null defalut 0,
                                                m9_amount decimal(10,2) not null defalut 0,
                                                m10_qty decimal(10,2) not null defalut 0,
                                                m10_amount decimal(10,2) not null defalut 0,
                                                m11_qty decimal(10,2) not null defalut 0,
                                                m11_amount decimal(10,2) not null defalut 0,
                                                m12_qty decimal(10,2) not null defalut 0,
                                                m12_amount decimal(10,2) not null defalut 0,
                                                flag int not null default 0
                            )';
                            --execute sql;
                            
                            
        
                            --更新地总
                            
                            sql = 'update ' ||  tmpfilename2 || ' as a  ';
                            
                            sql = sql || ' set username = b.username, usercode = b.usercode ';

                            sql = sql || ' from account as b ';
                            
                            sql = sql || ' where departmentid=b.department_id and b.statecode=''0'' ';
                            
                            
                            --execute sql;
                            
                            --更新部门
                            
                            sql = 'update ' ||  tmpfilename2 || ' as a  ';
                            
                            sql = sql || ' set departmentname = b.name,bigareaid=b.parentid ';
                            
                            sql = sql || ' from department as b ';
                            
                            sql = sql || ' where departmentid=b.id ';
                            
                            
                            --execute sql;
                            
                            
                            --更新大区

                            sql = 'update ' ||  tmpfilename2 || ' as a  ';

                            sql = sql || ' set bigareaname=b.name,bgid=b.parentid  ';

                            sql = sql || ' from department as b  ';

                            sql = sql || ' where bigareaid=b.id ';
                            

                            --execute sql;
                            

                            --更新事业部

                            sql = 'update ' ||  tmpfilename2 || ' as a  ';

                            sql = sql || 'set bgname=b.name  ';

                            sql = sql || ' from department as b  ';

                            sql = sql || ' where bgid=b.id ';
                --拼接月份字符串，例如：查询从202501--202506，
                --            
                    cond1 = ' sum(case when yearmonth= ';
                    cond2 = ' then qty else 0 end)  as ';
                    cond3 = ' then amount else 0 end)  as ';
                            --execute sql;
                    columnList=',';
                    WHILE ibeginmonth <= iendmonth LOOP
                            columnList =  columnList || cond1||'''' || cast(ibeginmonth as VARCHAR) || ''''|| cond2 ;
                            columnList =  columnList ||'数量' || cast(ibeginmonth as VARCHAR) ||',';
                            columnList =  columnList || cond1||'''' || cast(ibeginmonth as VARCHAR) || ''''|| cond3 ;
                            columnList =  columnList ||'金额'|| cast(ibeginmonth as VARCHAR) ||',';
                            ibeginmonth = ibeginmonth+1;
                    END LOOP;
                RAISE NOTICE '拼接字符串： %',columnList;
                            
                            
                            
                        --  汇总表
             sql = 'create table ' || tmpfilename2 || ' as ';
                        
                        sql = sql || 'select clientId,departmentid,goodscode,
                    
                        sum(case when yearmonth=''202501'' then qty else 0 end)     as t202501销量,
                        sum(case when yearmonth=''202501'' then amount else 0 end)  as t202501金额,
                        sum(case when yearmonth=''202502'' then qty else 0 end )    as t202502销量,
                        sum(case when yearmonth=''20252'' then amount else 0 end)   as t202502金额,
                        sum(case when yearmonth=''202503'' then qty else 0 end)     as t202503销量,
                        sum(case when yearmonth=''202503'' then amount else 0 end ) as t202503金额,
                        sum(case when yearmonth=''202504'' then qty else 0 end)     as t202504销量,
                        sum(case when yearmonth=''202504'' then amount else 0 end)  as t202504金额,
        
                        sum(case when yearmonth=''202505'' then qty else 0 end )    as t202505销量,
                        sum(case when yearmonth=''202505'' then amount else 0 end)  as t202505金额,                        
                        sum(case when yearmonth=''202506'' then amount else 0 end)  as t202506金额,
                        sum(case when yearmonth=''202506'' then qty else 0 end)     as t202506销量,
                        sum(case when yearmonth=''202507'' then amount else 0 end)  as t202507金额,    
                        sum(case when yearmonth=''202507'' then qty else 0 end)     as t202507销量,
                        sum(case when yearmonth=''202508'' then amount else 0 end)  as t202508金额,
                        sum(case when yearmonth=''202508'' then qty else 0 end)     as t202508销量,
                        
                        sum(case when yearmonth=''202509'' then amount else 0 end)  as t202509金额,
                        sum(case when yearmonth=''202509'' then qty else 0 end)     as t202509销量,
                      sum(case when yearmonth=''202510'' then qty else 0 end)     as t202510销量,                        
                        sum(case when yearmonth=''202510'' then amount else 0 end)  as t202510金额,
                        sum(case when yearmonth=''202511'' then qty else 0 end)     as t202511销量,                        
                        sum(case when yearmonth=''202511'' then amount else 0 end)  as t202511金额    ,
                        sum(case when yearmonth=''202512'' then qty else 0 end)     as t202512销量,                        
                        sum(case when yearmonth=''202512'' then amount else 0 end)  as "202512金额"    ';
                        

                        sql = sql || ' from ' ||  tmpfilename;

                        sql = sql || ' group by clientId,departmentid,goodscode ';
                        
                        execute sql;
                        RAISE NOTICE '获取横表查询语句： %',sql;
                        
                            sql = 'alter table '||  tmpfilename2  ||' REPLICA IDENTITY FULL';
                            execute sql;
                        RAISE NOTICE '本轮执行完毕=====================>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>';
                        
                        return tmpfilename3;
                            
                
    end;


    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100